Hi,

Just made a little search and found an article on conditional check constraints...

Are they "better" than the trigger solution?

CREATE TABLE GeneralInfo (EntityType INT, EntityId INT, CONSTRAINT PK_GeneralInfo PRIMARY KEY (EntityType, EntityId))

CREATE TABLE Customers (Id INT, Name VARCHAR(50), CONSTRAINT PK_Customers PRIMARY KEY (Id))

CREATE TABLE Suppliers (Id INT, Name VARCHAR(50), CONSTRAINT PK_Suppliers PRIMARY KEY (Id))

GO

CREATE FUNCTION CheckEntityExists(@EntityType INT, @EntityId INT) RETURNS INT AS

BEGIN

DECLARE @ret INT = 0

SELECT @ret = COUNT(*) FROM (SELECT 1 Record FROM Customers WHERE @EntityType = 0 AND Id = @EntityId UNION ALL SELECT 1 FROM Suppliers WHERE @EntityType = 1 AND Id = @EntityId) t

RETURN @ret

END

GO

ALTER TABLE GeneralInfo ADD CONSTRAINT CheckEntityExistsConstraint CHECK (dbo.CheckEntityExists(EntityType, EntityId) > 0)

GO

INSERT INTO GeneralInfo (EntityType, EntityId) VALUES (0, 1)

GO

--ERROR:

--Msg 547, Level 16, State 0, Line 1

--The INSERT statement conflicted with the CHECK constraint "CheckEntityExistsConstraint". The conflict occurred in database "DBA_Teste", table "dbo.GeneralInfo".

INSERT INTO Customers (Id, Name) VALUES (1, 'Customer 1')

INSERT INTO GeneralInfo (EntityType, EntityId) VALUES (0, 1)

-- OK.

DELETE FROM Customers

-- OK. Should not be allowed to delete since the record is "referenced"..

But there's a problem with this conditional constraints... or I just don't know how to do it (most probably)... I can delete from the customers table and have "bad" data on GeneralInfo table...

Is there a way to add a condition check constraint on delete, or just like the questions says "ON DELETE", the only way to do this is with a ON DELETE trigger on the Customers table?

Thanks,

Pedro