Mastering SQL for Data Science: A Comprehensive Guide Day-4

Mastering SQL for Data Science: A Comprehensive Guide 

Day-4

Constraints

Ø  Introduction:

Maintaining security and integrity of a database is the most important factor. Such limitations have been enforced on the data, and only that data which satisfies the conditions will actually be stored for analysis. If the data gathered fails to satisfy the conditions set, it is rejected. This technique ensures that the data is stored in the database will be valid, and has integrity. Rules, which are enforced on data being entered and prevents user from entering invalid data into tables are called constraints.

1.    Not Null constraints:

Often there may be records in a table that do not have values for every field, either because the information is not available at the time of data entry or because the field is not applicable in every case. Oracle will place a null value in the column in the absence of a user-defined value. By default every column will accept null values.

A nullvalue is different from a blank or a zero. We can say that null means undefined. Null are treated specially by oracle. When any column is defined as not null, it implies that a value must be entered into the column. Remember that not null constraints can be applied on column level only.

E.g: create table emp (emp_no char(5) not null, name varchar2(10));

Output: table created

    This above declaration is specifying the first column will not accept null values.

 2.    Check constraints:

    Business rule validations can be applied to a table column by using check constraints. Check constraints must be specified as a logical expression that evaluates either to true or false.

E.g: Create table customer (cust_no char(3) check (cust_no like ‘c%’),cname varchar2(10));

 Limitations:

1. The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated.
2. The condition cannot contain subqueries or sequence. 

3.    Unique constraints:

The purpose of unique key is to ensure that information in the columns is unique. That is the value entered in columns defined the unique constraints must not be repeated across the column. This type of constraints permits multiple entries of null into the column. These null values are clubbed at the top of the column in the order in which they were entered into the table.

Rules:

1. Unique key will not allow duplicate values.
2. Unique index is created automatically.
3. A table can have more than one unique key which is not possible in primary key.
4. Unique key cannot be long or long row data type.
5. Unique key can combine upto 16 columns in a composite unique key.

E.g: create table d_dept (dept_no char(4) unique, dname varchar2(15)); 

4.    Primary key constraints:

     A primary key is one or more columns in a table used to uniquely identify each row in the table. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key. A primary key column in a table has special attributes;

It defines the column, as a mandatory column (the column cannot be left blank), as the Not Null attribute is active. The data held across the column must be UNIQUE.

    A single column primary key is called a simple key. A multicolumn primary key is called a composite primary key.

 Features of primary key:

1. Primary key is a column or a set of columns that uniquely identifies a row. Its main purpose is the Record uniqueness.
2. Primary key will not allow duplicate values and null values.
3. Primary key is not compulsory but it is recommended.
4. Primary key cannot be long or long raw data type.
5. Primary key helps to identify one record from another record and also helps in relating tables with one another. 
6. Only one primary key is allowed per table.
7. Unique index is created automatically if there is a primary key.
8. One table can combine upto 16 columns in a composite primary key.

 E.g: create table d_dept (dept_no char(4) primary key, deptname varchar2(20));

5.    Referential Integrity constraints: (Foreign Key)

In this category there is only one constraints and it is foreign key references. To establish a parent-child or a master –detail relationship between two tables having a common column, we make use of referential integrity constraints.

Foreign key represent relationship between tables. A foreign key is a column whose values are derived from the primary key or unique key. The table in which the foreign key is defined is called a foreign table or detail table. The table that defines the primary key or unique keys and is referenced by the foreign key is called the primary key/ table or master table. 

Features of foreign key:

1. Foreign key is a column that references columns of a table and it can be the same table also.
2. Parent that is being referenced has to be unique or primary key.
3. Child may have duplicates and nulls but unless it is specified.
4. Foreign key constraint can be specified on child but not on parent.
5. Parent record can be deleting provided no child record exist.
6. Master table cannot be updated if child record exists.

 Rules of foreign key:

1. Rejects an insert or update of a value, if a corresponding value does not currently exists in the master key table.
2. If the on delete cascade option is set, a delete operation in the master table will trigger a delete operation for corresponding records in all detail tables. 
3. Rejects a delete from the master table if corresponding records in the detail table exist.
4. Requires that the foreign key column and the constraint column have matching data type.
5. Must reference primary key or unique columns in primary table.

Syntax: create table <table_name>( column_name1 <data_type>(<size>) references  

           <table_name>(<column_name>/<primary_key>),<column_name2>  

           <data_type>(<size>));

E.g: create table dept(dept_no references d_dept(dept_no), city varchar2(10));