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

Unique Key

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

  1. Unique key is another of the most commonly in use constraint in MS SQL.
  2. Multiple Unique key can be defined in a table.
  3. It can be reference by Foreign key to set the relation between tables.
  4. Unique key guarantees the value of the column to be Unique only.
  5. It’ll allow user to insert only 1 NULL value in the Unique column, if the column is set to accept NULL value.
  6. It can be defined in column with NULL or NOT NULL.
  7. It can be defined on a single column or on multiple columns or composite columns in a table.
  8. By default, Unique key creates a Unique Non Clustered Index in the table.
  9. At any time, if we disable the Unique Non Clustered default index created with the Unique Key, we can still access the table.
  10. However the property of the Unique key is not going to work now ( from above point 6) though we have the Unique present in the table. It means if you try to insert duplicate values or NULL value in the unique key column it is possible.
  11. Unique key never sort the data. It is the property of index. If we create the Clustered Index it’ll do the sort otherwise by default it’ll not with non clustered index.
  12. Unique key can also be created with Unique Clustered Index.
  13. On deleting the Unique Key manually, it’ll automatically drop the index associated with it(Clustered/Non-Clustered) and on deleting the Index associated with Unique key it’ll automatically drop the Unique key.
  14. We can define, Unique key and Non Clustered Index on different columns only if we define the Unique Key with Clustered Index.
  15. Anybody can define Unique key in existing table with data. It can be achieved by remove the duplicate values and remove multiple NULL values as Unique key accepts one NULL value.
  16. Any need to change the column’s datatype or need to drop the column (Column which is having the Unique key) entirely will require first DROP of the UNIQUE constraint on the column prior to your action.

Examples

–Example1: Default Unique Key, which will create a Unique Non Clustered Index on ID column
create table test
(
        Id int unique,
        name varchar(50) not null
)
–Example2: Default Unique Key, which will create a Unique Non Clustered Index on NOT NULL ID column
create table test
(
        Id int not null unique,
        name varchar(50) not null
)
— Example3: Default Unique Key, which will create a Unique Clustered Index on ID column
create table test
(
         Id int not null unique clustered,
         name varchar(50) not null
)
— Example4: Composite Unique Key on multiple columns
create table test
(
     Id int,
     name varchar(50) not null,
     unique (Id,name)
)
–Example5:  Multiple Unique Key on multiple columns
create table test
(
          Id int,
          name varchar(50) not null,
          unique (Id),
          unique (name)
)
–Example6:  Unique Key creation with constraint keyword. It’ll create a Unique key with name UK_ID and index with name UK_ID
create table test
(
       Id int,
       name varchar(50) not null,
       constraint UK_ID unique(ID)
)
–Example7:  Unique Key creation outside table definition. It’ll create a Unique key with name UK_ID and index with name UK_ID
create table test
(
       Id int,
       name varchar(50) not null
)
GO
— It creates a unique non clustered index
Alter table test add constraint [UN_ID] unique(ID)
— It creates a unique clustered index
Alter table test add constraint [UN_ID] unique clustered(ID)

To check or verify the index created on the table, we can use the below command

SELECT *
FROM sys.indexes I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON S.schema_id = T.schema_id
WHERE
T.Name = ‘test’                             — Table name
AND S.Name = ‘dbo’                    — Schema name/optional

To get detail information about all the Unique keys created in all the tables in the current database.

SELECT
@@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS TableName,   
c.name ColumnName,
t1.name as ColDataType,
c.max_length as ColMaxLength,
c.precision as ColPrecision,
c.scale as ColScale,
(case c.is_identity when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_IdentityType,
(case c.is_nullable when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_NullableType,
kc.name ConstraintName,
(Case i.is_disabled When 0 Then ‘No’ When 1 Then ‘Yes’ End) As UK_Index_IsDisable,
(case i.is_unique when 1 Then ‘Yes’ When 0 Then ‘No’ End) AS UK_Index_IS_Unique,
(Case i.index_id When 0 Then ‘Heap’ When 1 Then ‘Clustered’ Else ‘NonClustered’ End) As UK_IndexType,
(Case i.type
When 0 Then ‘Heap’
When 1 Then ‘Clustered’
When 2 Then ‘Nonclustered’
When 3 Then ‘XML’
When 4 Then ‘Spatial’
When 5 Then ‘Clustered columnstore’             –Applies to: SQL Server 2014 & Above
When 6 Then ‘Nonclustered columnstore’      –Applies to: SQL Server 2012 & Above
When 7 Then ‘Nonclustered hash’                    –Applies to: SQL Server 2014 & Above
End) AS UK_IndexSubType,
(case ic.is_descending_key when 0 Then ‘ASC’ When 1 Then ‘DSC’ End) AS UK_Index_SortOrder,
(case ic.is_included_column when 0 Then ‘No’ When 1 Then ‘Yes’ End) AS Col_IsIncluded
from
sys.key_constraints kc
inner join sys.objects o
on o.object_id = kc.parent_object_id
inner join sys.indexes i        
on i.name = kc.name
AND i.is_primary_key<>1               — get only the indexes associated with unique key
inner join sys.index_columns ic
on ic.object_id = kc.parent_object_id and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = kc.parent_object_id and c.column_id = ic.column_id
INNER JOIN sys.types t1                   — Get the column datatypes
ON c.user_type_id = t1.user_type_id
Where
kc.type=’UQ’                                      — For Unique key=UQ
order by 3,4

To drop or remove the Unique 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].[test] DROP CONSTRAINT [UN_ID]

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

Primary Key

In this blog, we’ll discuss about  Primary key.

  1. Primary key is one of the most commonly in use constraint in MS SQL.
  2. Only 1 Primary key can be defined in a table.
  3. By default, the column of the table automatically become NOT NULL  if Primary key is defined with column name while creating the table.
  4. It can be reference by Foreign key to set the relation between tables.
  5. Primary key guarantees the value of the column to be NOT NULL and Unique.
  6. It can be defined on a single column or on composite columns in a table.
  7. By default, primary key creates a Unique Clustered Index in the table.
  8. On disabling the Clustered Index which was created with Primary key, the table will become not accessible. To get the table back we need to rebuild that index.
  9. On disabling the Non Clustered Index which was created with Primary key, the table will remain accessible but the Primary key constraint will stop working.It means the column value can be modified or new value can be insert as NULL or duplicate values. (Note : If the column value is corrupted with NULL or duplicate data, then we cannot rebuild the Non-Clustered index. Because when we try to rebuild the index it’ll activate the Primary key and also do a validation check on the existing data,so in this case it’ll fail to validate the data.)
  10. Primary key never sort the data. It is due to the behavior of the Unique Clustered Index.
  11. Primary key can also be created with Unique Non Clustered Index.
  12. In case we have a table without Primary key but clustered index defined on a column and later on we tried to add a new column and set it as Primary key; it’ll automatically create a unique non-clustered index on the Primary key instead of clustered index. This is the second case where Primary key is defined with non-clustered index.
  13. On deleting the Primary Key manually, it’ll drop the index associated with it(Clustered/Non-Clustered) and on deleting the Index associated with Primary key it’ll automatically drop the Primary key.
  14. We can define, Primary key and Clustered Index on different columns only if we define the Primary Key with Non-Clustered Index.
  15. Anybody can define primary key in existing table with data. It can be achieved by making the column NOT NULL and remove the duplicate values.
  16. Any need to change the column’s datatype or need to drop the column (Column which is having the Primary key) entirely will require first DROP of the PRIMARY constraint on the column prior to your action.

Examples

–Example1: Default Primary Key, which will create a Unique Clustered Index on ID column
create table test
(
   Id int not null primary key,
   name varchar(50) not null
)
–Example2: Default Primary Key, which will create a Unique Clustered Index on ID column & Auto NOT NULL will be set for ID column
create table test
(
   Id int primary key,
   name varchar(50) not null
)
–Example3: Primary Key with Unique Non-Clustered index on ID column
create table test
(
   Id int primary key nonclustered,
   name varchar(50) not null
)
–Example4: Primary Key on multiple columns / composite primary key
create table test
(
   Id int not null,
   name varchar(50) not null,
   primary key (Id,name)
)
–Example5: Primary Key creation with constraint keyword, which will create a Unique Clustered Index on ID column
create table test
(
  Id int not null,
  name varchar(50) not null,
  constraint PK_ID primary key(ID)
)
–Example6: Primary Key creation outside table defination. It’ll create a Primary key with name PK_ID and a unique index with name PK_ID
create table test
(
  Id int not null,
  name varchar(50) not null
)
GO
— It creates a unique clustered index
Alter table test add constraint [PK_ID] primary key(ID)
— It creates a unique non clustered index
Alter table test add constraint [PK_ID] primary key nonclustered(ID)

To check or verify the index created on the table, we can use the below command

SELECT *
FROM sys.indexes I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON S.schema_id = T.schema_id
WHERE
T.Name = ‘test’                        — Table name
AND S.Name = ‘dbo’               — Schema name/optional

To get the detail information about all the Primary key defined in all the tables in the current database. (Note : PK=Primary Key. In case the primary key is a composite type, then the below query shows 2 entries in the result set.)

SELECT
@@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS TableName,   
c.name ColumnName,
t1.name as ColDataType,
c.max_length as ColMaxLength,
c.precision as ColPrecision,
c.scale as ColScale,
(case c.is_identity when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_IdentityType,
(case c.is_nullable when 1 Then ‘Yes’ Else ‘No’ End) AS Col_Is_NullableType,
kc.name ConstraintName,
(Case i.is_disabled When 0 Then ‘No’ When 1 Then ‘Yes’ End) As PK_Index_IsDisable,
(case i.is_unique when 1 Then ‘Yes’ When 0 Then ‘No’ End) AS PK_Index_IS_Unique,
(Case i.index_id When 0 Then ‘Heap’ When 1 Then ‘Clustered’ Else ‘NonClustered’ End) As PK_IndexType,
(Case i.type
When 0 Then ‘Heap’
When 1 Then ‘Clustered’
When 2 Then ‘Nonclustered’
When 3 Then ‘XML’
When 4 Then ‘Spatial’
When 5 Then ‘Clustered columnstore’             –Applies to: SQL Server 2014 & Above
When 6 Then ‘Nonclustered columnstore’    –Applies to: SQL Server 2012 & Above
When 7 Then ‘Nonclustered hash’                   –Applies to: SQL Server 2014 & Above
End) AS PK_IndexSubType,
(case ic.is_descending_key when 0 Then ‘ASC’ When 1 Then ‘DSC’ End) AS PK_Index_SortOrder,
(case ic.is_included_column when 0 Then ‘No’ When 1 Then ‘Yes’ End) AS Col_IsIncluded
from
sys.key_constraints kc
inner join sys.objects o
on o.object_id = kc.parent_object_id
inner join sys.indexes i        
on i.name = kc.name
AND i.is_primary_key=1                         — get only the indexes auto created with primary key
inner join sys.index_columns ic
on ic.object_id = kc.parent_object_id and ic.index_id = i.index_id
inner join sys.columns c
on c.object_id = kc.parent_object_id and c.column_id = ic.column_id
INNER JOIN sys.types t1                            — Get the column datatypes
ON c.user_type_id = t1.user_type_id
Where
kc.type=’PK’                                                  — For Primary=PF
order by 3,4

To drop or remove the Primary 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].[test] DROP CONSTRAINT [PK_ID]

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

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.