add constraint 1

[peac3]

Hi guys,

I need to design a new table and add constraint column to check the value which refer to column of other table like foreign key.

But for foreign key we need to refer to the column which primary key or unique column right?

Just wondering whether there is possibility to add constraint to refer other table but NOT primary key/unique column?

Any idea will be appreciated.

Cheers guys,

 

[Crowley16]

you can add a foreign key relationship between any column in any table, irrespective of whether it's a key or not, as long as the datatypes match.

Although you'll get problems if both the columns contain duplicates.

--------------------
Procrastinate Now!

 

[peac3]

No, I can't.

I have tried other normal column and it complains saying that the key is not primary key.

 

[Crowley16]

one you your columns needs to be unique, so, just add a unique constraint (index) on one of them, preferably the parent...

Oh, just read your question more carefully, no pk AND no unique index...

Yeah, that will pose a problem then...

just wondering, why can't you add a unique index?

--------------------
Procrastinate Now!

 

[peac3]

because that column cannot be unique as there must be same value for different records.

 

[RiverGuy]

You can use a trigger. Try this on a test database

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

CREATE TABLE ChildTable (
ChildTableID INT IDENTITY NOT NULL PRIMARY KEY,
ParentTableID INT NOT NULL
)
GO

CREATE TRIGGER tChildTable ON ChildTable 
FOR INSERT, UPDATE
AS
BEGIN
	IF EXISTS (SELECT 1 FROM INSERTED WHERE ParentTableID NOT IN (SELECT ParentTableID FROM ParentTable))
	BEGIN
		RAISERROR ('ParentTableID does not exist in ParentTable', 16, 1);
		ROLLBACK TRANSACTION;
		RETURN 
	END;
END
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

 

[peac3]

Looks great
Thanks RiverGuy,
I will try this on monday.

Cheers mate,

 

[peac3]

Hi Riverguy,

It works well, I was wondering whether it can reduce the performance alot.

Have u used this alot, how do u think the performance different.

Thanks mate,

 

[RiverGuy]

If you have an index on the "ParentTableID" column in the parent table, it shouldn't be an issue.

 

[peac3]

Hi Riverguy,

If I want to make trigger of the combination two columns,
how do I do that?
This is what I've tried but doesn't work

CREATE TABLE ParentTable (
ParentTableID INT NOT NULL, --Not Unique
ParentTableSubID INT NOT NULL --Not Unique
)
GO

CREATE TABLE ChildTable (
ChildTableID INT IDENTITY NOT NULL PRIMARY KEY,
ParentTableID INT NOT NULL,
ParentTableSubID INT NOT NULL --Not Unique
)
GO


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

Thanks man,

 

[RiverGuy]

You could use a join, something like

IF (SELECT COUNT(*) FROM
	(SELECT a.ParentTableID
	FROM INSERTED a
	LEFT OUTER JOIN ParentTable b
	  ON a.ParentTableID = b.ParentTableID
	  AND a.ParentTableSubID = b.ParentTableSubID
	WHERE b.ParentTableID IS NULL) x) 
> 0
BEGIN
        RAISERROR ('ParentTableID,ParentTableSubID does not exist in ParentTable', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN   
END;