Friday, 4 May 2012

Sql Server material 7


Foreign Key Constraint: it is a column or combination of columns that is used to establish and enforce a link between the data in two tables. In a foreign key reference, a link is created between two tables when the column(s) in a table reference the column(s) that hold the primary key of other table, which becomes a foreign key in the first table.  
For example, the Dept.Deptno table below has a link to the Emp.Deptno table because there is a logical relationship between Dept table and Emp table. The Deptno column in the Emp table matches the primary key column of the Dept table. The Deptno column in the Emp table is the foreign key to the Dept table. In this case the value that is going to be inserted into the Deptno column of the Emp table should be present in the Deptno column of the Dept table or should be a null values.

-          In this case the Dept table is called as Parent table and Emp table is called as Child table.
-          Dept.Deptno is called as Reference Key column on which either Primary Key Constraint or Unique Constraint has to be imposed.
-          Emp.Deptno is called as Foreign Key column on which the Foreign Key Constraint has to be imposed, with this only the link gets established between the 2 tables.
Create Table Dept (Deptno int Constraint Deptno_Pk Primary Key, DName varchar(50), Loc varchar(50))
1.      Insert into Dept values (10, 'Marketing', 'Mumbai')
2.      Insert into Dept values (20, 'Sales', 'Chennai')
3.      Insert into Dept values (30, 'Finance', 'Delhi')
4.      Insert into Dept values (40, 'Production', 'Kolkota')

-Creating Foreign Key Constraint in column level:
Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, Sal Money, Comm Money, Deptno int Constraint Deptno_Ref References Dept (Deptno))

-Creating Foreign Key Constraint in column level: while defining constraint in table level we need to explicitly use the Foreign Key clause to specify the Foreign Key Column:
Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, Sal Money, Comm Money, Deptno int,
Constraint Deptno_Ref Foreign Key (Deptno) References Dept (Deptno))
-Now when we try to insert values into the Emp table the Deptno what we give should be only the 4 values (10, 20, 30, 40) present in the dept table or a null value, if we try to insert any other value the insert statement fails.
1.      Insert into Emp Values (1001, 'Suresh', 'President', NULL, '01/01/78', 5000, NULL, 10)
2.      Insert into Emp Values (1002, 'Ramesh', 'Manager', 1001, '01/01/78', 4000, NULL, 20)
3.      Insert into Emp Values (1003, 'Ravi', 'Manager', 1001, '01/01/78', 3500, NULL, 30)
4.      Insert into Emp Values (1004, 'Vijay', 'Manager', 1001, '01/01/78', 4000, NULL, 40)
5.      Insert into Emp Values (1005, 'Ajay', 'Salesman', 1003, '02/04/79', 3000, NULL, 50)
-In this case the first 4 statements gets executed but last statement fails because the Deptno given is not present in the dept table.
The Foreign Key constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.
By using cascading referential integrity constraints, you can define the actions that the SQL Server 2005 takes when a user tries to delete or update a key value in the master table to which existing foreign keys point.
The REFERENCES clauses of the CREATE TABLE statements support the ON DELETE and ON UPDATE clauses:

-ON DELETE <NO ACTION | CASCADE | SET NULL | SET DEFAULT>
-ON UPDATE <NO ACTION | CASCADE | SET NULL | SET DEFAULT>

NO ACTION is the default if ON DELETE or ON UPDATE is not specified.

ON DELETE NO ACTION: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, an error is raised and the DELETE statement will not execute.

ON UPDATE NO ACTION: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, an error is raised and the UPDATE statement will not execute.

When NO ACTION is specified we can delete or update or delete rows in the master table if they are referenced by child table rows, but we can perform those operations when we use CASCADE, SET NULL AND SET DEFAULT CLAUSES:

ON DELETE CASCADE: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are also deleted.

ON UPDATE CASCADE: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all the foreign key values will also be updated to the new value specified for the key.

ON DELETE SET NULL: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to NULL, provided the foreign key column allows NULL values into it.

ON UPDATE SET NULL: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to NULL, provided the foreign key column allows NULL values into it.

ON DELETE SET DEFAULT: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default value set, NULL becomes the implicit default value of the column.

ON UPDATE SET DEFAULT: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default value set, NULL becomes the implicit default value of the column.  

We can use any of the rules beside the column as following:
Deptno int Constraint Deptno_Ref References Dept (Deptno) on delete cascade on update cascade
In the same way you can use any rule there and also not mandatory to specify both the delete and update rule, we can use any rule what we require.

<<< Previous                                                                                                                     Next >>>

No comments:

Post a Comment