Foreign Key Part III / Cascading referential integrity constraint

In this blog, we’ll discuss about the Cascading referential integrity constraint.

When we are creating the Foreign key constraint references to a Primary/Unique key, by default the Cascading referential integrity constraint for the Foreign key is set to “NO ACTION“. [It means we cannot modify or delete the Primary/Unique key once it is references by a Foreign key.]

Cascading referential integrity constraint have 2 rules namely.

  1. ON UPDATE : What happens to the Foreign key column value “ON UPDATE” of the Primary/Unique key value which it is referencing.
  2. ON DELETE : What happens to the Foreign key column value “ON DELETE” of the Primary/Unique key value which it is referencing.

The above two rules can have any one of the below action types

  1. NO ACTION: This is the default behavior.It means Primary/Unique key delete/update statement will be ROLLBACK and no action will be taken or both Foreign & Primary/Unique key. It is applicable by default, so it’ll continue to work even if we didn’t specify it explicitly.
  2. CASCADE : In this case any delete/update query on Primary/Unique key value will be cascading same to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will also be deleted from the table automatically.
  3. SET NULL : In this case any delete/update query on Primary/Unique key value will be set NULL values to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will be set to NULL automatically.
  4. SET DEFAULT : In this case any delete/update query on Primary/Unique key value will be set Default values (of the Foreign key column) to all the Foreign keys referring to it.Example on deleting the Primary/Unique key record all the foreign key record referencing it will be set to Default values (of the Foreign key column) automatically.

Examples

–Table A
create table Dep
(
DID int Primary key,
DepName varchar(50)
)

–Example1A: Table B with foreign key references to Table A with No ACTION (Default)
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID)
Name varchar(50),
Gender char(1),
)

–Example1B: Table B with foreign key references to Table A with No ACTION
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE NO ACTION
Name varchar(50),
Gender char(1),
)

–Example2: Table B with foreign key references to Table A with CASCADE
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE CASCADE
Name varchar(50),
Gender char(1),
)

–Example3: Table B with foreign key references to Table A with SET NULL
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE SET NULL
Name varchar(50),
Gender char(1),
)

–Example4: Table B with foreign key references to Table A with SET DEFAULT
create table Employee1
(
EmpId int primary key,
DepId int foreign key references Dep(DID) on UPDATE SET DEFAULT
Name varchar(50),
Gender char(1),
)

For DELETE action just replace the word UPDATE in the foreign key references line with DELETE

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 II

Advertisements

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

Foreign Key Part I

In this blog, we’ll discuss about Foreign Key.

Note : Here we are assuming the 2 tables.Example : Multiple employees can work in 1 department. Here DepartmentID is the Primary Key in Department table(1st table) and DepId is the Foreign Key (referencing) in Employee table (2nd table) referenced to Department table.

  1. Foreign key is use for setting the relation between 2 or more tables.
  2. 1 table can have multiple Foreign keys.
  3. We can define a maximum of 253 Foreign keys in a table or a table can have maximum 253 columns use as referenced for Foregin key in other table.
  4. To set a Foreign key, we need below column property to be satisfied
    1. Column in both the tables must have same datatype and length.
    2. Column of the 1st table with which the second table want to refer for the relation must be Primary Key or Unique Key.
    3. Column in the second table must be set to NOT NULL which is used as a Foreign key in that table. (Default property)
  5. After setting the Foreign key relation between 2 tables, below points are in effect
    1. No values, except those that are present in the referenced table (1st table) key column are allowed to be inserted / updated in the referencing table (2nd table). (Default property)
    2. Deletion/Update of the column value in the referenced table (1st table) key column are not allowed. (Default property)
  6. Foreign key allows duplicate values in the referencing table (2nd table).
  7. Foreign key can be either disable or removed. ( It is not possible with Primary Key / Unique Key).
  8. Foreign key cannot be defined in Temp table (both Local & Global) or Temp Variable. (Note : Interesting point is, SQL server create the table successfully (Local/Global/Temp Variable) without Foreign key with a message “Skipping FOREIGN KEY constraint ‘#Tablename’ definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.)
  9. Any need to change the column’s datatype or need to drop the column (Column which is having the Foreign key) entirely will require first DROP of the FOREIGN constraint on the column prior to your action.

Examples

–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,
      DepId int foreign key references Department(DepartmentID),
      Name varchar(50),
     Gender char(1),
)
–Example2: Table B with foreign key references to Table A
create table Employee1
(
       EmpId int primary key,
       DepId int,
       Name varchar(50),
       Gender char(1),
       Foreign key(DepId) references Department(DepartmentID)
)
–Example3: Table B with foreign key references to Table A with constraint keyword & create a foreign key with name FK_Department_DepID
create table Employee1
(
         EmpId int primary key,
         DepId int,
        Name varchar(50),
        Gender char(1),
       CONSTRAINT FK_Department_DepID FOREIGN KEY(DepId) REFERENCES            Department (DepartmentID)
)
–Example4: Table B with foreign key references to Table A create outside table
create table Employee1
(
    EmpId int primary key,
    DepId int,
     Name varchar(50),
     Gender char(1)
)
–Without constraint keyword and constraint name
ALTER TABLE [dbo].[Employee1] WITH CHECK ADD FOREIGN KEY([DepId]) REFERENCES [dbo].[Department] ([DepartmentID])
–With constraint keyword and constraint name
ALTER TABLE [dbo].[Employee1] WITH CHECK ADD CONSTRAINT [FK_Department_DepID] FOREIGN KEY([DepId]) REFERENCES [dbo].[Department] ([DepartmentID])

–Example5: Table B with multiple foreign key references to Table A and Table C
create table Employee1
(
    EmpId int primary key,
    DepId int foreign key references Department(DepartmentID),  
     Name varchar(50),
     Gender char(1) foreign key references Gender(GID),
)

–Example6A:  Table A with Composite Primary key
create table DepartmentDetail
(
  DepartmentID int not null,
  HODID int not null,
  primary key(DepartmentID,DepName)
)

–Example6A: Table B with Composite foreign key references to Table A 
create table Employee1
(
  EmpId int primary key,
  DepId int,
  HODID int,
  Name varchar(50),
  Foreign key(DepId,HODID) references DepartmentDetail(DepartmentID,HODID)
)

To drop or remove the Foreign constraint , we need to provide the constraint name. If the constraint name is auto generated then get it GUI from the left side in SSMS under the table => Goto constraint => click on the constraint and drag it over the command window.

ALTER TABLE [dbo].[Employee1] DROP CONSTRAINT [FK_Department_DepID]

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

Click here to read the next part :– Foreign Key Part II

Integrity Constraint in SQL

In this blog, we’ll discuss about Integrity constraint in SQL Server.

Integrity constraint are rules implemented in the tables to guarantee the accuracy, completeness, or consistency of data in tables and keep safe the business logic of the schema design.Constraints are part of a database schema definition

In SQL, all the constraints are mainly grouped together in 4 major types, they are:

  1. Primary integrity : which can be done by using
    1. PRIMARY KEY
    2. UNIQUE KEY
  2. Referential integrity : which can be done by using
    1. FOREIGN KEY
  3. Domain integrity : which can be done by using
    1. DEFAULT
    2. NOT NULL
    3. CHECK
  4. User Defined integrity : which can be done be using
    1. RULE

Please click on each constraints for detail explanation.

To get the details (like Constraint_Type,Constraint_Name,Constraint_Keys, enable / disable etc. ) of all the constraint define in a table execute the system procedure with table name.

EXEC sp_helpconstraint ‘dbo.Employee1’

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