sql server new users: Is there a way to enforce Z or N cardinality through SQL Server ?

TIA
Howard

[quoted text, click to view]

Z cardinality is easy. Just use a common primary key:

CREATE TABLE foo (x INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE bar (x INTEGER NOT NULL PRIMARY KEY REFERENCES foo (x));

N cardinality is harder because SQL Server supports neither deferrable
constraints or ANSI-style constraints with subqueries. If you want to
implement N <= 5 then you could do:

CREATE TABLE foo (x INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), n INTEGER NOT
NULL CHECK (n BETWEEN 1 AND 5), PRIMARY KEY (x,n));

To enforce that N always = 5 isn't strictly possible unless you do it
after the table is populated because there would be no way to populate
the referenced table without violating the constraint. You could use a
trigger to enforce the constraint only when the referencing table
changes. Like:

CREATE TRIGGER trg_bar ON bar AFTER INSERT, UPDATE, DELETE
AS
IF EXISTS
(SELECT x
FROM bar AS B
WHERE EXISTS
(SELECT *
FROM Inserted AS I
WHERE I.x = B.x)
GROUP BY x
HAVING COUNT(*)>5)
BEGIN
ROLLBACK TRAN
RAISERROR('No more than 5!',16,1)
END

IF EXISTS
(SELECT x
FROM bar AS B
WHERE EXISTS
(SELECT *
FROM Deleted AS D
WHERE D.x = B.x)
GROUP BY x
HAVING COUNT(*)<5)
BEGIN
ROLLBACK TRAN
RAISERROR('No less than 5!',16,1)
END

GO

INSERT INTO foo VALUES (1);
INSERT INTO bar (X,n) VALUES (1,1);
INSERT INTO bar (X,n) VALUES (1,2);
INSERT INTO bar (X,n) VALUES (1,3);
INSERT INTO bar (X,n) VALUES (1,4);
INSERT INTO bar (X,n) VALUES (1,5);

/* will fail: */
DELETE FROM bar WHERE n = 5;

Hope this helps.

--
David Portas
SQL Server MVP
--