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]