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 >>>
<<< Previous Next >>>
No comments:
Post a Comment