SQL Constraints (Part -5)

 SQL constraints are conditions / rules that apply on the data columns of a table which is used to limit the type of data that goes inside the table.

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command.

There are 3 types of Constraints:

A)     Key Constraint

B)     Domain Constraint

C)     Referential Integrity Constraint

The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.

A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
   The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

CONSTRAINTS	Uses
NOT NULL	Restricts NULL value from being inserted into a column.
CHECK	Verifies that all values in a field satisfy a condition.
DEFAULT	Automatically assigns a default value if no value has been specified for the field.
UNIQUE	Ensures unique values to be inserted into the field.
INDEX	Indexes a field providing faster retrieval of records.
PRIMARY KEY	Uniquely identifies each record (row) in a table.
FOREIGN KEY	Ensures referential integrity for a record in another table.

As you can see above that No two Rows are similar to each other that means table should not contain duplicates. Otherwise, final output will change.

A)     Key Constraint:

In order to stop duplicates enter into the table, we need to create a primary constraint which is called “Key Constraint”.

Example: Create table from an existing database.

Create table Persons (

ID INT,

LastName varchar (200),

FirstName varchar (200),

Age int,

PRIMARY KEY (ID) );

PRIMARY KEY neither allows duplicates into the table nor NULL values into the Column.

Now insert data into the table.

               insert into Persons values(1,”Suri”,”Meenu”,18),(2,”Aya”,”Veda”,24);

Now, try to insert a duplicate.

See, Primary Key will not allow duplicates for Integers but not for varchar.

insert into persons values(3,”Dada”,”Victor”,NULL);

Null Values are allowed in Age, etc., but not in Column ID (as you can see below)

See No PRI KEY in Students Table (see below)

Use ‘alter’ & ‘ADD PRIMARY KEY’ commands for above Students table.

You can also remove PRI KEY by using “DROP” command

Note: A table will have only One Primary Key but can have more Unique Keys.

A)    Referential Integrity Constraint (Foreign Key Constraint)

In order to relate two tables as parent-child relationship, we need Foreign Key.

Let’s say:

a)      Parent à Persons (ID is the Primary Key)

b)     Child à Orders (OrderID is Primary Key while ID is the Foreign Key)

 

First, create table Orders (

OrderID int,

OrderNumber int,

ID int,

PRIMARY KEY (OrderID),

FOREIGN KEY (ID) REFERENCES Persons(ID));

Create table into Orders;

Select Persons tables;

Now try to insert 4^th table in Orders table;

See as data ID is not present in Parent (Persons) tables, so not showing in Child (Orders) table.

            Drop Foreign Key;

Now you can see 4^th Order ID (Child) after dropping Foreign Key.

C)   Domain Constraint

Domain constraint defines a valid set of values for a table attribute also specifies all the possible values that the attribute can hold like integer, character, date, time, string, etc.

It is used to restrict the values to be inserted in the column or relation.

3        types of Domin Constriants:

a)      NOT NULL constraint

b)     Check constraint

c)      UNIQUE constraint

 

a)     NOT NULL Constraint:

If we specify a field in a table to be NOT NULL, then the field will never accept null value. That is, you will be not allowed to insert a new row in the table without specifying any value to this field. 

create table Persons_NOTNULL (

ID int NOT NULL,

LastName varchar(200) NOT NULL,

FirstName varchar(200) NOT NULL,

Age int

);

b)    UNIQUE Key Constraint:

Provide uniqueness in table. Ensures that all values in a column are different.

Create a table after choosing from available databases.

Describe Table to see the contents;

See above table is showing 2 Primary Keys using Unique Key method.

Unique Key will not allow duplicates in both Cust_ID & Last_Name, means first 2 columns should be different.

c)   CHECK Constraint:

This ensures that all the values in a column satisfy certain conditions.

Check constraint will not allow the age int less than 25.

D)   Auto Increment Constraint:

To automatically increase values in the table, mostly Primary Key will be Auto Increment.

No need to give ID values.

E) Dropping Constraints

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_SK;

Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.