Archive

Posts Tagged ‘Foreign Key’

DELETE from Parent and all its child tables with Foriegn Key (no DELETE ON CASCADE) – MSDN TSQL forum

April 8, 2015 Leave a comment

–> Question:

I have a database with many tables. I would like to Delete all rows with practiceID=55 from all Parents tables and all corresponding rows from its child tables. Tables are linked with foreign key constraints (but there is no ON DELETE CASCADE).
Can any one tell how to write a generalized code for removing rows from both parent and child tables.

Query should pick parent table one by one and delete rows with practiceID=55 and all corresponding rows from its child tables.
 

–> My Answer:

I this case you need to create query by using system catalog tables/views, link.

Check this query below:

DECLARE @Deletestr VARCHAR(max) = ''
SELECT @Deletestr = @Deletestr +
'DELETE FROM ' + object_name(fk.referenced_object_id) + 'WHERE ParentId = 55
GO
'
from sys.foreign_keys fk
join sys.foreign_key_columns fc
on fk.object_id = fc.constraint_object_id
where object_name(fk.parent_object_id) = 'SalesOrderHeader'

PRINT (@Deletestr)

EXEC (@Deletestr)

Like above query get the linked tables (FK) info, and finally with the output create Dynamic DELETE statement.
 

Ref Link.


‘NOCHECK’ vs ‘WITH NOCHECK’… while creating disabled Foreign Key (FK) constraint

March 14, 2012 2 comments

I dicussed about disabling FK constraints in one of my previous post to UPDATE/DELETE records to avoid some conflicts, check this [link] for more info.

There is an option while adding a Constraint to a table:

-- Syntax:
ALTER TABLE TABLE_NAME WITH NOCHECK 
ADD CONSTRAINT FK_CONSTRAINT_NAME FOREIGN KEY(COL_NAME_ID)
REFERENCES OTHER_REF_TABLE (OTHER_TABLE_COL_NAME_ID)

But this doesn’t disable the FK constraint, instead it creates the FK constraint without checking existing records.

You will need to issue a separate ALTER TABLE statement to disable the FK constraint:

-- Syntax:
ALTER TABLE TABLE_NAME NOCHECK CONSTRAINT FK_CONSTRAINT_NAME

So, what’s the difference here when we apply ‘WITH NOCHECK’ vs ‘NOCHECK’?

Let’s check this by taking the [Sales].[SalesOrderDetail] table from AdventureWorks database:

USE [AdventureWorks2008R2]
GO

-- Here I just created the script of [Sales].[SalesOrderDetail] table and stripped off the unwanted parts and changed the table name to [Sales].[SalesOrderDetailTemp]
CREATE TABLE [Sales].[SalesOrderDetailTemp](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SpecialOfferID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetailTemp_SalesOrderID_SalesOrderDetailID] 
 PRIMARY KEY CLUSTERED (
	[SalesOrderID] ASC,
	[SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Let's create a FK constraint with 'WITH NOCHECK' option
ALTER TABLE [Sales].[SalesOrderDetailTemp]  
	WITH NOCHECK 
	ADD CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID] FOREIGN KEY([SalesOrderID])
	REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
ON DELETE NO ACTION
GO

Now, go to Object Explorer check the Table -> Keys. Right click on the FK and select Modify. Check the following option on the FK Relationship popup box:
– General -> Check existing data on Creation or Re-Enabling = No
– Table Designer -> Enforce foreign key constraint = Yes

This shows that the FK constriant is still enabled but it didn’t validated the existing records while creating the FK.

Now to really disable the FK we will just have to use the NOCHECK option without using the WITH keyword:

ALTER TABLE [Sales].[SalesOrderDetailTemp]  
	NOCHECK CONSTRAINT [FK_SalesOrderDetailTemp_SalesOrderHeader_SalesOrderID]
GO

Now, check again the Table -> Keys in Object Explorer. You will see the second option ‘Enforce foreign key constraint’ has changed to ‘No’ in the FK Relationship popup box.

-- Final cleanup
DROP TABLE [Sales].[SalesOrderDetailTemp]
GO

So, finally we saw the difference in using the NOCHECK option by using ‘WITH’ and without ‘WITH’ keyword.
And you cannot directly add a Disabled Constraint to a table. You will need to create the FK Constraint first then only you can disable it by using another ALTER TABLE statement.

SQL Basics – Working with Foreign Key (FK) constraints

December 27, 2009 3 comments

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables, as per MSDN BoL.
 

–> Foreign Key (FK) Constraint

1. We can create a FK by defining a FOREIGN KEY constraint while Creating or modifying (Altering) a table.

2. We can define a FK in a table that points to a PK or UK in another table.
   – Like Employee & Department, shown in video-demo below.

3. A FK constraint can reference columns within the same table, these are called self-referencing tables. For example: Employee & Manager Relationship.
 

–> Referential Integrity:

1. The FK constraint enforces Referential Integrity by not allowing values in the Child table outside form the domain of the Parent table.

2. This also disallows any changes to the Parent table, like deleting any row or modifying PK value in the Parent table.
 

–> Check the video on Foreign Key:

FK Constraint
 

–> Department and Employee table FK relationship:

FK Constraint
 

–> SQL Code used in above video:


-- 1. Parent table:

CREATE TABLE [dbo].[Department](
	 [DeptID]	int IDENTITY (101, 1) PRIMARY KEY
	,[DeptName]	nvarchar(100)
	,[isActive]	bit DEFAULT(1)
)
GO

INSERT INTO [dbo].[Department] ([DeptName])
SELECT 'HR'
UNION ALL
SELECT 'Finance'
UNION ALL
SELECT 'Admin'
UNION ALL
SELECT 'IT'

select * from [dbo].[Department]
GO


-- 2. Child Table:

-- Method #1.a : with Column inline
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL FOREIGN KEY REFERENCES Department(DeptID)
	,[isActive]		bit DEFAULT(1)
)
GO

-- Method #1.b : with Column inline & Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY REFERENCES Department(DeptID)
	,[isActive]		bit DEFAULT(1)
)
GO

-- Method #2.a : with Explicit
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
		FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
)
GO

-- Method #2.b : with Explicit & Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
		CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
)
GO

-- Method #3 : with ALTER Table Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
)
GO

-- SSMS or ALTER Table stmt:

-- Generated by SSMS - Table Designer:
ALTER TABLE dbo.Employee ADD CONSTRAINT
	FK_Employee_Department FOREIGN KEY
	(
	DeptID
	) REFERENCES dbo.Department
	(
	DeptID
	)

-- OR --

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
GO


INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Manoj Pandey', 'M', 101)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Deepak B', 'M', 102)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Jhon B', 'M', 103)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Mariya Y', 'F', 104)

select * from [dbo].[Employee]
GO


-- REFERENTIAL INTEGRITY:

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Mary J', 'F', 105)
--Msg 547, Level 16, State 0, Line 115
--The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EmployeeID_DeptID". The conflict occurred in database "TestManDB", table "dbo.Department", column 'DeptID'.
--The statement has been terminated.

DELETE FROM [dbo].[Department]
WHERE DeptID = 102
--Msg 547, Level 16, State 0, Line 118
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeID_DeptID". The conflict occurred in database "TestManDB", table "dbo.Employee", column 'DeptID'.
--The statement has been terminated.

DROP TABLE [dbo].[Department]
--Msg 3726, Level 16, State 1, Line 121
--Could not drop object 'dbo.Department' because it is referenced by a FOREIGN KEY constraint.

GO


-- Allowed updates:

select * from [dbo].[Department]

UPDATE [dbo].[Department]
SET DeptName = 'Human Resource'
WHERE DeptID = 101

select * from [dbo].[Department]

GO


-- Final Cleanup:

DROP TABLE [dbo].[Employee]
GO
DROP TABLE [dbo].[Department]
GO

Check Primary Key & Unique Key constraints

To know about more on Constraints and their types check this blog post.