Add constraint using triggers – SQL Server

I need to add constraint column to check the value which refer to column of other table like foreign key. However that column was not the primary key. So i left with two options, one is triggers and another is constraint.

This implementation is using triggers

CREATE TABLE ParentTable
(
ParentID INT NOT NULL --Not Unique
)
GO

CREATE TABLE ChildTable
(ChildID INT IDENTITY NOT NULL PRIMARY KEY,
ParentID INT NOT NULL)

GO
--using triggers
CREATE TRIGGER tChildTable ON ChildTable
FOR INSERT, UPDATE
AS
BEGIN
	IF EXISTS (SELECT 1 FROM INSERTED WHERE ParentID NOT IN (SELECT ParentID FROM ParentTable))
	BEGIN
		RAISERROR ('ParentTableID does not exist in ParentTable', 16, 1);
		ROLLBACK TRANSACTION;
		RETURN
	END;
END

GO

GO

INSERT INTO ParentTable SELECT 1
INSERT INTO ParentTable SELECT 1
INSERT INTO ParentTable SELECT 3
INSERT INTO ChildTable SELECT 1 --Works
GO
INSERT INTO ChildTable SELECT 3 --Works
GO
INSERT INTO ChildTable SELECT 2 --Does not work
GO
SELECT * FROM ChildTable
GO
DROP TABLE ParentTable
GO
DROP TABLE ChildTable
GO

And this one is using check contraint

CREATE TABLE ParentTable
(
ParentID INT NOT NULL --Not Unique
)
GO

CREATE TABLE ChildTable
(ChildID INT IDENTITY NOT NULL PRIMARY KEY,
ParentID INT NOT NULL)

GO
--Using function/check constarint
create function [fn_CK_Constraint]
(@ChildId  int)
returns int
as
begin
	declare @count int
	Select
		@count = COUNT(1)
	from
		ParentTable
	where
		ParentId = @ChildId
	return @count
end

go

ALTER TABLE ChildTable
WITH CHECK ADD
CONSTRAINT [CK_Constraint]
CHECK  (([dbo].[fn_CK_Constraint]([ChildId])>=(1)))
GO
INSERT INTO ParentTable SELECT 1
INSERT INTO ParentTable SELECT 1
INSERT INTO ParentTable SELECT 3
INSERT INTO ChildTable SELECT 1 --Works
GO
INSERT INTO ChildTable SELECT 3 --Works
GO
INSERT INTO ChildTable SELECT 2 --Does not work
GO
SELECT * FROM ChildTable
GO
DROP TABLE ParentTable
GO
ALTER TABLE ChildTable DROP CONSTRAINT [CK_Constraint]
GO
DROP TABLE ChildTable
GO
drop function [fn_CK_Constraint]
Tags: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*