How to Create Foreign Key Constraint with ON DELETE SET NULL Option in SQL Server - SQL Server / TSQL Tutorial Part 81

Scenario:

You are working as SQL Server developer, you need to create two tables with Primary -Foreign Key Relationship. You want to create Foreign Key Constraint with setting if record will be deleted from Referenced Table (Primary Key Column Table), it should not be deleted from Parent Table ( Foreign Key Constraint Table) instead the value should be updated to Null.

Solution:

We can use ON DELETE SET NULL with Foreign Key Constraint definition to implement above requirement. 

Let's perform by using below script.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES
     Customer(CustomerId) ON DELETE SET NULL
    )

    --insert sample data
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

    insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,1)

Check the data in tables by using Select query 

How to create Foreign Key Constraint with ON DELETE SET NULL in SQL Server 

Let's delete the row from Referenced Table( Primary Key Column Table) and check if records still exists in Parent Table ( Foreign Key Constraint Table) and column value is updated to Null.

    --Delete the Record from Referenced Table(PK Column Table)
    Delete from dbo.Customer
    where CustomerId=1

How to use ON DELETE SET NULL option with Foreign Key Constraint in SQL Server

As we can see that the record is deleted from Referenced Table( Primary Key Column Table) but still present in Parent Table ( Foreign Key Constraint Table)  but value is updated to Null as expected.

Video Demo : How to Create Foreign Key Constraint with ON DELETE SET NULL Option in SQL Server

How to create Foreign Key Constraint with ON DELETE CASCADE in SQL Server - SQL Server / TSQL Tutorial Part 80

Scenario:

You are working as SQL Server developer, you need to create two tables with Primary -Foreign Key Relationship. You want to create Foreign Key Constraint with setting if record will be deleted from Referenced Table (Primary Key Column Table), it should be deleted from Parent Table ( Foreign Key Constraint Table) as well.

Solution:

SQL Server let us use the the setting with Foreign Key Constraint called On DELETE CASCADE. If this rule is implemented, whenever record is deleted from Referenced Table( Primary Key Column Table), it will also be deleted from Parent Table ( Foreign Key Constraint Table).

Let's test this scenario with below script.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES
     Customer(CustomerId) ON DELETE CASCADE
    )

    --insert sample data
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

Check the data in tables by using select query.

How to enable ON Delete CASCADE rule with Foreign Key Constraint in SQL Server

Let's delete the row from Referenced Table( Primary Key Column Table) and see if it also deletes from Parent Table ( Foreign Key Constraint Table) 

    --Delete the Record from Referenced Table(PK Column Table)
    Delete from dbo.Customer
    where CustomerId=1

Check the tables again to see if record is deleted from both tables due to ON Delete Cascade rule on Foreign Key Constraint.

How to use ON Delete Cascade to delete records from multiple Tables in SQL Server Table

As we can see that the records are deleted from both tables due to ON DELETE CASCADE rule of Foreign Key Constraint. 

Video Demo:How to create Foreign Key Constraint with ON DELETE CASCADE in SQL Server

How to create Foreign Key Constraint With ON UPDATE CASCADE in SQL Server - SQL Server / TSQL Tutorial Part 79

Scenario:

In previous posts, we learn that if we have Foreign key Constraint with default setting and we try to update the value in column in Reference Table which is used as Reference Column in Foreign Key Constraint, we get error. We discussed multiple ways to handle the situation, please check this link.

Foreign Key Constraint does provide the option to set the Cascading action, we can create Foreign Key Constraint with Cascading Update. 

If Update Cascading settings is used, when we update the value in Referenced Table , it will also update the value in parent table (Foreign Key Table) column.

Let's test this scenario. Create two tables dbo.Customer and dbo.Orders with Foreign Key Relationship by given script

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES
     Customer(CustomerId) ON UPDATE CASCADE
    )


    --insert sample data
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

    insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,1)

Let's check the data in tables by using select query

How to create Foreign Key Constraint with Update Cascade in SQL Server

Let's run our update statement on CustomerId in dbo.Customer table and see if it also update the column value in dbo.Orders for Customer_id.

    update dbo.Customer
    set Customerid=100

Let's check the data again in our tables 

How to enable Update Cascading with Foreign Key Constraint in SQL Server 

As we can see that the value is also updated in dbo.Orders.Customer_id column.

Video Demo:How to create Foreign Key Constraint With ON UPDATE CASCADE in SQL Server

How to Generate Script To Enable All Foreign Key Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 78

Scenario: 

You are working as SQL Server developer, You need to prepare scripts to enable all the Foreign Key Constraint in a database which are disabled. This could happen, maybe somebody has disabled Foreign Key Constraints for a data load where they don't care about violation of referential integrity and forgot to enable them later.

Solution:

First of all let's get the list of Foreign Key Constraints with status by using below query

--Get List of Foreign Key Constraints if Enabled or Disabled
    USE YourDatabaseName
    GO
    Select 
    Schema_name(Schema_id) as SchemaName,
    object_name(Parent_object_id) as TableName,
    name as ForeignKeyConstraintName,
    Case When Is_disabled=1 Then 'No'
    ELSE 'Yes' End as IsEnabled
    from sys.foreign_keys

How to check if Foreign Key Constraint is Disabled or Enabled in SQL Server Database

Now let's generate scripts to enable the Foreign Key Constraints which are disabled in SQL Server Database by using below query.

USE YourdatabaseName
go
-- Enable Foreign Key Constraints Script 
SELECT distinct 'ALTER TABLE ' 
+ '['+ Schema_name(FK.schema_id) 
+ '].['+ OBJECT_NAME(FK.parent_object_id) 
+ ']'+ ' CHECK  CONSTRAINT ' 
+ '[' + FK.name + ']' AS EnableConstraintQuery
 FROM   sys.foreign_keys AS FK
 where is_disabled=1

How to generate script to enable all Foreign Key Constraints in SQL Server Database 

Copy the results and run in SSMS to enable disabled Foreign Key Constraints in SQL Server Database.

Video Demo : How to generate script to enable all Foreign Key Constraint in SQL Server

How to disable all Foreign Key Constraint in SQL Server Database - SQL Server / TSQL Tutorial Part 77

Scenario: 

You are working as SQL Server developer, You need to provide the scripts to disable all the Foreign Key Constraint in SQL Server database.  This might be scenario where you need to load one time data and you are ok if data violate referential integrity.

Solution:

Let's get the list of Foreign Key Constraints from a SQL Server Database before we generate the disable script.

--Get List of Foreign Key Constraints if Enabled or Disabled
    USE YourDatabaseName
    GO
    Select 
    Schema_name(Schema_id) as SchemaName,
    object_name(Parent_object_id) as TableName,
    name as ForeignKeyConstraintName,
    Case When Is_disabled=1 Then 'No'
    ELSE 'Yes' End as IsEnabled
    from sys.foreign_keys

How to Check if Foreign Key Constraint is Enabled or Disabled in SQL Server

Now let's generate script to Disable Foreign Key Constraint in SQL Server Database

USE YourdatabaseName
go
-- Drop Foreign Key Constraints Script 
SELECT distinct 'ALTER TABLE ' 
+ '['+ Schema_name(FK.schema_id) 
+ '].['+ OBJECT_NAME(FK.parent_object_id) 
+ ']'+ ' NOCHECK  CONSTRAINT ' 
+ '[' + FK.name + ']' AS DisableConstraintQuery
 FROM   sys.foreign_keys AS FK
 where is_disabled=0

How to generate Script to Disable All Foreign Key Constraints in SQL Server Database

Video Demo : How to disable all Foreign Key Constraint in SQL Server Database

The UPDATE statement conflicted with the REFERENCE constraint - SQL Server / TSQL Tutorial Part 76

Scenario:

You are working as SQL Server developer, You wrote an update statement for one of the table and getting below error.


Msg 547, Level 16, State 0, Line 32
The UPDATE statement conflicted with the REFERENCE constraint "FK_". 
The conflict occurred in database "YourDatabaseName", table "SchemaName.YourTableName", column 'ColumnName'.
The statement has been terminated.

How to resolve this issue?

Solution:

Let's create this error first by using below script. We are going to create two tables dbo.Customer and dbo.Orders. The tables has Primary-Foreign Key Relationship.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES Customer(CustomerId)
    )


    --insert sample data
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

    insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,1)

How to update record when Column is referenced by Foreign Key Constraint in SQL Server

Now let's say if you feel that CustomerId value is incorrect in dbo.Customer and need to be updated. You wrote below update statement to update CustomerId to 100.

    update dbo.Customer
    set Customerid=100

You will get below error.

Msg 547, Level 16, State 0, Line 33

The UPDATE statement conflicted with the REFERENCE constraint "FK__Orders__Customer__1ED998B2". 

The conflict occurred in database "YourDatabaseName", table "dbo.Orders", column 'Customer_id'.

The statement has been terminated.

As there is no Customer_id value=100 in dbo.Orders table, You can't update the record in reference table. Now you thought that let's fix the Parent table first ( dbo.Orders) and then I can update the dbo.Customer table.

    update dbo.Orders
    set Customer_Id=100

Again you got the error as shown below, because we don't have CustomerId=100 available in dbo.Customer table.

Msg 547, Level 16, State 0, Line 36

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__Orders__Customer__1ED998B2".

 The conflict occurred in database "YourDatabaseName", table "dbo.Customer", column 'Customerid'.

The statement has been terminated.

From here we can come with with multiple solutions

1) Instead of updating the record, Insert the record in Reference Table ( Dbo.Customer), Then update the record in Parent table (Dbo.Orders) and finally delete the existing records from Reference Table.

    --Insert Record in Reference Table First
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (100,'Aamir','Shahzad','000-000-00')

    --Update the Records in Parent Table 
        update dbo.Orders
    set Customer_Id=100

    --Delete the old record from Reference Table
    Delete from dbo.Customer
    where CustomerId=1

Check the records in table now.

How to update Column Value when referenced by Foreign Key Constraint in SQL Server 

2) Disable the Foreign Key Constraint and Update the Values Manually

Another solution can be, disable the Foreign Key constraint, update the records and finally enable the Foreign key again.

--Find the Foreign Key Constraint with Table Name
    USE YourDatabaseName
    GO
    Select 
    Schema_name(Schema_id) as SchemaName,
    object_name(Parent_object_id) as TableName,
    name as ForeignKeyConstraintName
    from sys.foreign_keys

Disable the Foreign Key Constraint by using below statement

Syntax

ALTER TABLE SchemaName.ParentTableName

NOCHECK CONSTRAINT Constraint_Name

I used below statement to disable Foreign Key constraint on dbo.Orders table.

--Disable Foregin Key by using NOCHECK
ALTER TABLE dbo.Orders
NOCHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E

--Run Update Statements
    update dbo.Customer
    set Customerid=100

    update dbo.Orders
    set Customer_Id=100

Enable Foreign Key Constraint Syntax

ALTER TABLE SchemaName.ParentTableName

CHECK CONSTRAINT Constraint_Name

I execute below script to Enable Foreign Key Constraint on dbo.Orders table.

--Enable Foreign Key Constraint by using CHECK
ALTER TABLE dbo.Orders
CHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E

Video Demo : The UPDATE statement conflicted with the REFERENCE Constraint

How to Drop Foreign Key Constraint in SQL Server Database - SQL Server / TSQL Tutorial Part 75

Scenario:

You are working as SQL Server developer and you need to prepare the script to drop the Foreign Key Constraint which was created on dbo.Orders table.

Solution:

Let's create dbo.Customer and dbo.Orders sample tables and include Foreign Key Constraint as part of create table by using below script.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES Customer(CustomerId)
    )

Once the tables are created and Foreign Key Constraint as well, you can use below statement to find the Foreign Key Constraint Name with table name.

    USE YourDatabaseName
    GO
    Select 
    Schema_name(Schema_id) as SchemaName,
    object_name(Parent_object_id) as TableName,
    name as ForeignKeyConstraintName
    from sys.foreign_keys

Find Foreign Key Constraint Name in SQL Server with Table Name

Now we know the constraint name, we can go ahead and write our drop constraint statement.

Syntax for Drop Foreign Key Constraint on Table

Alter Table SchemaName.TableName

Drop Constraint Constraint_Name

I used below statement to drop FK__Orders__Customer__164452B1 Foreign Key Constraint.

    Alter table dbo.Orders
    Drop Constraint FK__Orders__Customer__164452B1

If you are interested to generate scripts to drop all Foreign Key Constraints in a database, Check this link.

Video Demo : How to Drop Foreign Key Constraints in SQL Server

Could not drop object because it is referenced by a FOREIGN KEY constraint - SQL Server / TSQL Tutorial Part 74

Scenario:

You are working as SQL Server DBA or Developer, You need to drop a table from a database. When you execute drop table SchemaName.TableName statement, you get below error.

Msg 3726, Level 16, State 1, Line 12
Could not drop object 'SchemaName.TableName' because it is referenced by a FOREIGN KEY constraint.

Now we know that the table is referenced by Foreign Key Constraint. The problem is how to find which table has that Foreign Key Constraint that is referencing to this table.

Solution:

1) There are many ways to get this information. We can use system views to find the name of table which has the Foreign Key Constraint which is referencing our Primary Table.

SELECT Schema_Name(Schema_id) as TableSchemaName,
  object_name(FK.parent_object_id) ParentTableName,
  object_name(FK.referenced_object_id) ReferenceTableName
       FROM sys.foreign_keys AS FK
       WHERE object_name(FK.referenced_object_id)='YourTableName'
       and Schema_Name(Schema_id)='YourTableSchemaName'

I executed above script for my customer table which is present in dbo schema and here is what I got.

How to find which table's Foreign Key is referencing to Table in SQL Server.

Now we know that Ord is the table which has the Foreign Key Constraint which is referencing our table. We can go ahead and drop the foreign key Constraint and then drop our table.

2) Use System Stored Procedure sp_fkeys

We can use system Stored Procedure to get the Foreign Key Constraints information which are referencing to our table. If my table name is Customer, I can run script as below

EXEC sp_fkeys 'Customer'

How to get Foreign Key Constraint name for a Table in SQL Server

The sp_fkeys returns very detailed information, few of the columns are not shown in snapshot above. here is the list of columns it will return.

• PKTABLE_QUALIFIER
• PKTABLE_OWNER
• PKTABLE_NAME
• PKCOLUMN_NAME
• FKTABLE_QUALIFIER
• FKTABLE_OWNER
• FKTABLE_NAME
• FKCOLUMN_NAME
• KEY_SEQ
• UPDATE_RULE
• DELETE_RULE
• FK_NAME
• PK_NAME

Video Demo : Could not drop object because it is referenced by a Foreign Key Constraint

How to generate scripts to Re-create Foreign Key Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 73

Scenario:

You need to truncate all the tables in SQL Server database, when when you run truncate statement, you get error below error.
Msg 4712, Level 16, State 1, Line 43
Cannot truncate table 'SchemaName.TableName' because it is being referenced by a FOREIGN KEY constraint.

The easy way would be drop the Foreign Key Constraints, truncate the tables and recreate the Foreign Key Constraint again.

I wrote a post that you can use to generate Drop Foreign Key Constraints in a database. Click here. 
But before we drop them, we need to generate the create Foreign key Constraints scripts so we can run after truncating the table.

You can use below script to generate truncate table statement for all the user tables from a database.

Select 'Truncate table '+'['
+Schema_name(Schema_id)
+'].['+name+']' as TruncateTablesScript
from sys.tables
where is_ms_shipped=0

The below script can be used to re-generate Foreign Key Constraint in a database.

;With CTE_FK AS (
SELECT Schema_Name(Schema_id) as TableSchemaName,
  object_name(FK.parent_object_id) ParentTableName,
  object_name(FK.referenced_object_id) ReferenceTableName,
  FK.name AS ForeignKeyConstraintName,c.name as RefColumnName,
  cf.name as ParentColumnList
       FROM sys.foreign_keys AS FK
       INNER JOIN sys.foreign_key_columns AS FKC
               ON FK.OBJECT_ID = FKC.constraint_object_id
               INNER JOIN sys.columns c
          on  c.OBJECT_ID = FKC.referenced_object_id
                 AND c.column_id = FKC.referenced_column_id
                 INNER JOIN sys.columns cf
          on  cf.OBJECT_ID = FKC.parent_object_id
                 AND cf.column_id = FKC.parent_column_id
                 where fk.is_ms_shipped=0
                 )
                 Select 
                 'Alter table ['+TableSchemaName+'].['+ParentTableName+']' 
                 +' Add Constraint '+ForeignKeyConstraintName+ 
                 ' Foreign Key('+stuff((
                 Select ','+ParentColumnList
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '')+') References '+
                 '['+TableSchemaName+'].['+ReferenceTableName+']('+stuff((
                 Select ','+RefColumnName
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '')+')'
                 AS CreateForeignKeyConstraintScript,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName
                 from CTE_FK o
                 group by 
                 tableSchemaName,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName

How to generate script to re-create Foreign Key Constraint in SQL Server Database

Take the results from CreateForeignKeyConstraintScript Column. I suggest you to run the scripts in DEV or QA first to make sure all working fine before you run in Production.

Video Demo : How to create generate scripts to recreate foreign Key Constraints in SQL Server

How to drop Foreign Key Constraints in SQL Server Database for all the tables - SQL Server / TSQL Tutorial Part 72

Scenario:

You are working as SQL Server developer, you are asked to provide the scripts those can be used to drop Foreign Key Constraints on all the tables in a database if exists.

Solution:

We can use system view to generate the drop Foreign Key Constraints for all the tables in SQL Server Database by using below query.

USE YourdatabaseName
go
-- Drop Foreign Key Constraints Script 
SELECT distinct 'ALTER TABLE ' 
+ '['+ Schema_name(FK.schema_id) 
+ '].['+ OBJECT_NAME(FK.parent_object_id) 
+ ']'+ ' DROP  CONSTRAINT ' 
+ '[' + FK.name + ']' AS DropConstraintQuery
 FROM   sys.foreign_keys AS FK

How to drop all the Foreign Key Constraints in SQL Server Database

Execute above query and then take the results and run in SSMS to drop all the Foreign Key Constraint in database.

Video Demo : How to generate drop Foreign Key Constraint scripts for entire database in SQL Server

How to get Parent Table, Reference Table, Foreign Key Constraint Name and Columns in SQL Server - SQL Server / TSQL Tutorial Part 71

Scenario:

You are working as SQL Server Developer, you are asked to provide the query that should return all the parent tables, reference tables, Foreign Key Constraints and Columns used in Foreign Key Constraint definition.

Solution:

We can use the system views to gather this information. In our below query we will be using three

system views
sys.foreign_keys
sys.foreign_key_columns
sys.columns 

to answer the request. As we can have composite primary key columns used in Foreign Key Constraint, I have used FOR XML Path to concatenate rows into column so can provide list of columns in single row.

;With CTE_FK AS (
SELECT Schema_Name(Schema_id) as TableSchemaName,
  object_name(FK.parent_object_id) ParentTableName,
  object_name(FK.referenced_object_id) ReferenceTableName,
  FK.name AS ForeignKeyConstraintName,c.name as ReferencedColumnList,
  cf.name as ParentColumnName 
       FROM sys.foreign_keys AS FK
       INNER JOIN sys.foreign_key_columns AS FKC
               ON FK.OBJECT_ID = FKC.constraint_object_id
               INNER JOIN sys.columns c
          on  c.OBJECT_ID = FKC.referenced_object_id
                 AND c.column_id = FKC.referenced_column_id
                 INNER JOIN sys.columns cf
          on  cf.OBJECT_ID = FKC.parent_object_id
                 AND cf.column_id = FKC.parent_column_id
                 )
                 Select TableSchemaName,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName,stuff((
                 Select ','+ParentColumnName
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '') ParentColumnList
                 ,stuff((
                 Select ','+ReferencedColumnList
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '') RefColumnList
                 from CTE_FK o
                 group by 
                 tableSchemaName,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName

I executed above script on one of my database and here is output with Schema Name,Parent Table Name, Referenced Table Name, Foreign Key Constraint Name, Parent Column List and Reference Column List used in Constraint.

How to get Parent Table, Referenced Table,Foreign Key Constraint Name, Columns list in SQL Server

Video Demo : How to get Primary Key Table,Foreign Key Table and Constraint Name in SQL Server

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint - SQL Server / TSQL Tutorial Part 70

Scenario: 

You need to truncate a table but when you try to execute truncate table tableName. You get below error.

Msg 4712, Level 16, State 1, Line 43
Cannot truncate table 'SchemaName.TableName' because it is being referenced by a FOREIGN KEY constraint.

How would you truncate this table?

Solution:

As the table in involved in Foreign Key relationship, you need to drop the foreign key constraint first and then execute the truncate table statement.

Let's demo this example, I am going to create two table dbo.Customer and dbo.Orders and then create Foreign Key Constraint on one of the column of Dbo.Orders to dbo.Customer table.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT,
    CustomerId int
    )

--Create Foreign Key Constraint 
    Alter table dbo.Orders with Nocheck
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)

Now if I try to truncate dbo.Orders table, it will throw no error. But when I try to truncate dbo.Customer table it will throw error as dbo.Customer is begin references by Foreign Key Constraint.

How to truncate a table which is reference by Foreign Key Constraint in SQL Server

We can drop the constraint for now, truncate the table and then recreate Foreign key constraint.

To find the Foreign Key Constraints on a table, you can use below statement.

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('dbo.Customer')

How to get Constraint name from system tables in SQL Server

The below script can be used to generate drop Constraint statement for your table

SELECT 
    'ALTER TABLE ' 
    +  OBJECT_SCHEMA_NAME(parent_object_id) 
    +'.[' + OBJECT_NAME(parent_object_id) 
    +'] DROP CONSTRAINT ' 
    + name as DropFKConstraint
FROM sys.foreign_keys

WHERE referenced_object_id = object_id('dbo.Customer') 

How to drop Foreign Key Constraint on Table in SQL Server

Take the result for Drop Foreign Key Constraint and execute, After that run your truncate table statement to truncate table. It should complete without any error.

Video Demo : Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint in SQL Server - SQL Sever / TSQL Tutorial Part 69

Scenario:

You have created two tables dbo.Customer and dbo.Orders without having primary-foreign key relationship. After creating tables you inserted few records. Later you realized that you were supposed to add Foreign Key Constraint. When you tried to alter dbo.Orders table , you received error.

Create dbo.Customer and Dbo.Order Tables by using below script

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT,
    CustomerId int
    )

Insert sample records by using below script.

     INSERT INTO dbo.Customer 
    (CustomerId,FName, LName,SSN)
     VALUES
    (1,'Aamir','Shahzad','000-000-00')

    INSERT INTO dbo.Orders
    (OrderItemName,OrderItemAmt,Customerid)
    values ('TV',2,2)

Now let's add Foreign Key Constraint

    Alter table dbo.Orders
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)

When we execute above script, we get below error.

Msg 547, Level 16, State 0, Line 31
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Fk_CustomerId". The conflict occurred in database "YourDatabaseName", table "dbo.Customer", column 'Customerid'.

As dbo.Customer has value 1 for CustomerId column and in dbo.Orders table column CustomerId has value 2. The values does not match with each other. That is the reason we received above error.

Solutions:

1) Fix the data in second table (dbo.Orders)

We can fix the data in second table and update the CustomerId column values. Once we will have correct data that matches with our Primary Table ( Dbo.Customer.CustomerId), it will let us create Foreign Key Constraint without any issue.

2) Use Alter Table with Nocheck ( Ignore existing data)

If you don't care about relationship of existing data. You can use With NoCheck with alter table statement and it will ignore the check to validate data and create Foreign Key Constraint. Once the Foreign Key Constraint will be created, it will enforce integrity for any new records inserted.

    Alter table dbo.Orders with Nocheck
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)

Video Demo