Adding Constraints

Adding Primary Key:

1. Primary key enforces the uniqueness of the data in table
2. Data in the table is organized using the primary key as it creates clustered index
3. It also improves the performance of the WHERE clause etc

While creating Table:

    Just specify the 'primary key' attribute next to the column which you want to add as the primaryKey.

Example:

       Create Table Test(
                Id int primary Key,
                Name varchar(20)
         )

-- Compound Primary key:

       Create Table Test(
                Id int  Not NULL,
                Name varchar(20),
                LastName varchar(30)
                Primary key (Id,Name)
         )

• It'll add the primary key on both the columns (Id, Name).

After Creating Table:

1. We need to use ALTER TABLE syntax to add the primary key to table.

ALTER TABLE Test ADD constraint PrimaryKey_Id_Test primary key(Id)
GO

Adding New Primary Key Column:

     We can just specify the 'primary key' attribute at the end of the add column statement

ALTER TABLE [TABLE NAME] 

ADD [COLUMNNAME] DATATYPE primary key

GO

Foreign Key Constraint:

1. It'll enforce the rightness in the data, when data in one table is dependent on the other
2. It'll allow us to avoid accidentally delete the data in one table while it has dependents in other tables

While creating Table:

    Just specify the 'foreign key' attribute next to the column which you want to add as the foreign key.

Example:

       Create Table Test(

                Id int foreign key references ForeignTable(Id) On Delete [OPTION],

                Name varchar(20)

         )

After Creating Table:

ALTER TABLE Test 

ADD CONSTRAINT ForeignKey_Id_Test foreign key(id) 

references ForeignTable(Id) ON DELETE [OPTION]

GO

And you can specify 4 different key words for [OPTION], Which will let you set what should happen if you try to delete the row in ForeignTable, if there a row referencing the Id of the row which we are trying to delete in ForeignTable.

• ON DELETE RESTRICT (default ): It will gives the error message saying we should delete child rows referencing the Id should be deleted in Test table.
• ON DELETE NO ACTION: same as ON DELETE RESTRICT 
• ON DELETE CASCADE:If we try to delete row in ForeignTable It'll delete all the rows which are referencing the row. It will do automatic clean up. but we might end up deleting important information.
• ON DELETE SET NULL : If we try to delete row in ForeignTable It'll set the NULL for the foreign key column of all the rows which are referencing the row.

Default Constraint:

    Default constraint ensures to add the data when there is no data is specified to  the column while inserting data.

While Creating Table:

Example:

       Create Table Test(

                Id int primary Key,

                Name varchar(20) default 'NONAME'

         )

After Creating Table:

ALTER TABLE Test

add constraint default_Id_Test default 'NONAME' for Name

GO

Dropping constraints:

ALTER TABLE TEST DROP [Constraint Name]

GO

Example:

ALTER TABLE TEST 

DROP DF__Test__LastName__108B795B

GO

1. We can't delete the rows unless we delete the constraints on the tables. 
2. we can find all the constraints in "information_schema.table_constraints" table

Please free to comment if you need any other information.