DavidM

1) One to One - (One to (0 or 1))
2) One to Many - (One to (0 or n))
3) Many to Many

This serverly limits the practical implementation of a particular business model that requires a definate cardinality on one side.

The following T/SQL shows how to implement a One to <=5 Constraint and also how to modify the constraint to meet changing business requirements.

The example below will enforce the rule that a Cop can only have 5 or less Donut types.
Thanks to late checking we can build this schema as neatly as possible by referring to tables not yet created.

The UDF simply returns the current count of DonutTypes and the Check constraint enforces that the value returned be less than or equal to 5 for a particular Cop

The one side affect is that SQL Server hides the cost of this check, so it is fairly hard to determine how much extra load is placed on SQL.
CREATE FUNCTION DonutsPerCop
(@Cop VARCHAR(25))
RETURNS INT
AS
BEGIN
DECLARE @Return INT
SELECT @Return = COUNT(*) FROM Donuts WHERE Cop = @Cop
RETURN @Return
END
GO
--Cops
CREATE TABLE Cops (Cop VARCHAR(25) NOT NULL PRIMARY KEY, Rank VARCHAR(25) NOT NULL)
GO
--Donuts
CREATE TABLE Donuts (Cop VARCHAR(25) NOT NULL, DonutType VARCHAR(10) NOT NULL
    CONSTRAINT PK_Donuts PRIMARY KEY(Cop, DonutType),
    CONSTRAINT FK_CopsDonuts FOREIGN KEY (Cop) REFERENCES Cops (Cop),
    CONSTRAINT CHK_DonutsPerCop CHECK (dbo.DonutsPerCop(Cop) <=5))
GO

--Data
INSERT Cops (Cop, Rank)
   SELECT 'Bill', 'Officer'
   UNION ALL
   SELECT 'Fred', 'Detective'
   UNION ALL
   SELECT 'Gary', 'Officer'
   UNION ALL
   SELECT 'Simon', 'Detective'
   UNION ALL
   SELECT 'David', 'Officer'
   UNION ALL
   SELECT 'Eric', 'Detective'

INSERT Donuts (Cop, DonutType)
   SELECT Cop, 'Plain' FROM Cops
   UNION ALL
   SELECT Cop, 'Sprinkles' FROM Cops
   UNION ALL
   SELECT Cop, 'Chocolate' FROM Cops
   UNION ALL
   SELECT Cop, 'Strawberry' FROM Cops
   UNION ALL
   SELECT Cop, 'Yummy' FROM Cops WHERE Cop = 'Eric'
GO
SELECT Cop, COUNT(*) as DonutsPerCop
FROM Donuts
GROUP BY Cop
GO
--ACID Checks
--Failure (Eric already has 5)
INSERT Donuts (Cop, DonutType)
   SELECT Cop, 'Awful' FROM Cops WHERE Cop = 'Eric'
--Success (David only had 4)
INSERT Donuts (Cop, DonutType)
   SELECT Cop, 'Awful' FROM Cops WHERE Cop = 'David'
--Failure (David already has 5)
INSERT Donuts (Cop, DonutType)
   SELECT Cop, 'New Donut' FROM Cops WHERE Cop != 'Eric'
--Success
INSERT Donuts (Cop, DonutType)
   SELECT Cop, 'Caramel' FROM Cops WHERE Cop NOT IN ('Eric', 'David'
) GO
--Lets allow them 10 now...
ALTER TABLE Donuts DROP CONSTRAINT CHK_DonutsPerCop
GO
ALTER TABLE Donuts WITH CHECK ADD CONSTRAINT CHK_DonutsPerCop CHECK (dbo.
DonutsPerCop(Cop) <=10) GO
--Success
INSERT Donuts (Cop, DonutType)
   SELECT Cop, 'More' FROM Cops
GO
--Clean up
DROP TABLE Donuts, Cops
DROP FUNCTION DonutsPerCop
Feedback on possible problems would be much appreciated.

Death to the middle tier!