Foreign Key Part II

In this blog, we’ll discuss about some advance part of the foreign key and how to override the default property.

–Table A
create table Department
(
    DepartmentID int Primary key,
    DepName varchar(50)
)
–Table C
create table Gender
(
    GID int primary key,
    GenderType varchar(20)
)

–-Example1: Table B with foreign key references to Table A
create table Employee1
(
    EmpId int primary key,
    CONSTRAINT [FK_Department_DepID] FOREIGN KEY(DepId) REFERENCES Department (DepartmentID)
)

insert into Department values (1,’IT’),(2,’Accounts’),(3,’Admin’),(4,’Management’)
insert into Employee1 values (100,1),(200,2)

— not possible as DepId 5 is not present in Table A
insert into Employee1 values (500,5)

— disable the foreign key
ALTER TABLE EMPLOYEE1 NOCHECK CONSTRAINT [FK_Department_DepID]

— Now it is possible to insert any value
insert into Employee1 values (500,5)

— Enable the foreign key
ALTER TABLE Employee1 CHECK CONSTRAINT [FK_Department_DepID]

— Again not possible as DepId 7 is not present in Table A
insert into Employee1 values (700,7)

–What about the invalid record in Table B with DepId 5. By default on re-enable the FOREIGN key doesn’t verify the existing records. It is only concern about the new insert/modification values.

–To validate all the existing records and also validate the new insert/modification in the table,use the keyword “WITH CHECK
ALTER TABLE Employee1 WITH CHECK CHECK CONSTRAINT [FK_Department_DepID]

— On issue of the above statement it’ll give error and did not enable the FOREIGN key as we have an invalid record with DepId 5. So first remove it or modify it with correct data then issue the above command to enable the FOREIGN key.

–To add the FOREIGN key in existing table or outside table which can enforce the validation on the new data as well as check the validation of the existing data in the table, use the below command.

ALTER TABLE Employee1 WITH CHECK ADD CONSTRAINT [FK_Department_DepID] FOREIGN KEY(DepId) REFERENCES Department (DepartmentID)

–To add the FOREIGN key in existing table or outside table without any check or validation for existing data
ALTER TABLE Employee1 WITH NOCHECK ADD CONSTRAINT [FK_Department_DepID] FOREIGN KEY(DepId) REFERENCES Department (DepartmentID)

— OR
ALTER TABLE Employee1 ADD CONSTRAINT [FK_Department_DepID] FOREIGN KEY(DepId) REFERENCES Department (DepartmentID)

To get detail information about all the Foreign keys defined in the current database in all the tables. ( Note : FK = Foreign Key, DK=Default key)

SELECT
@@Servername AS ServerName,
DB_NAME() AS DB_Name,
OBJECT_SCHEMA_NAME(o1.object_id) AS SchemaName,
o1.name AS FK_Table,
c1.name AS FK_Column,
t.name AS FK_ColDataType,
c1.max_length AS FK_ColMaxLength,
c1.precision AS FK_ColPrecision,
c1.scale AS FK_Scale,
(Case c1.is_nullable When 0 Then ‘No’ Else ‘Yes’ End) AS FK_ColIsNULL,
fk.name AS FK_ConstraintName,
o2.name AS References_Table,
c2.name AS References_Column,
pk.name AS References_ConstraintName,
(CASE pk.type when ‘PK’ Then ‘Primary Key’ When ‘UQ’ Then ‘Unique Key’ END) AS References_ColType,
fk.delete_referential_action_desc AS FK_Delete_Action,
fk.update_referential_action_desc AS FK_Update_Action,
(CASE fk.is_disabled when 0 Then ‘Yes’ Else ‘No’ END) AS FK_EnableStatus,
(CASE fk.is_not_for_replication when 0 Then ‘Yes’ Else ‘No’ END) AS FK_EnforceForReplicattion,
(CASE fk.is_not_trusted when 0 Then ‘Yes’ Else ‘No’ END) AS FK_ValidateExisitingData,
fk.create_date AS FK_CreateDate,
fk.modify_date AS FK_ModifiedDate,
(Case When ISNULL(dk.name,’NA’)<>’NA’ Then ‘Yes’ Else ‘No’ End) AS FK_Col_Has_DefaultConstarint,
ISNULL(dk.name,’NA’) AS DefaultConstarintName,
ISNULL(cast(dk.create_date AS varchar),’NA’) AS DK_Const_CreateDate,
ISNULL(cast(dk.modify_date AS varchar),’NA’) AS DK_Const_ModifiedDate,
ISNULL(dk.definition,’NA’) AS DefaultConstDefinition
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1                                  — find the foreign key column name
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.types t                                           — Get the column datatypes
ON c1.user_type_id = t.user_type_id
INNER JOIN sys.columns c2                               — find the references table column name
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2                                  — find the references table name
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk               — find the Primary/Unique Parent                                                                                                                 –table reference col name
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
LEFT JOIN sys.default_constraints dk              — find the default key associated in the                                                                                                         –same col
ON dk.parent_object_id = o1.object_id
AND dk.parent_column_id = c1.column_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Click here to read about the previous part : —  Foreign Key Part I

Click here to read the next part :– Foreign Key Part III / Cascading referential integrity constraint

Advertisements

2 thoughts on “Foreign Key Part II

  1. Pingback: Foreign Key Part III / Cascading referential integrity constraint | DATABASE
  2. Pingback: Foreign Key Part I | DATABASE

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s