Using UDF's for n Cardinality relationships.

Tue Nov 18, 2003 by byrmol

SQL Server can only represent 3 types of "relationship" constraints (FK) declaratively

One to One - (One to (0 or 1))
One to Many - (One to (0 or n))
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!

Legacy Comments


Jeff 2003-11-19	re: Using UDF's for n Cardinality relationships. Very cool; UDF's are definitely useful for this. Constraints on calculated columns in your tables can be really useful as well. Another thing you can do, to be REALLY fancy, would be to add this to the end of your code: CREATE TABLE DonutsPerRank(Rank varchar(25) not null primary key, MaxDonuts int) GO INSERT DonutsPerRank SELECT 'Detective',10 union SELECT 'Officer',10 GO CREATE FUNCTION MaxPerRank(@Cop varchar(25)) RETURNS Int AS BEGIN RETURN (SELECT MaxDonuts FROM DonutsPerRank D INNER JOIN Cops C ON D.Rank = C.Rank WHERE C.Cop =@Cop) END GO ALTER TABLE Donuts DROP CONSTRAINT CHK_DonutsPerCop GO ALTER TABLE Donuts WITH CHECK ADD CONSTRAINT CHK_DonutsPerCop CHECK (dbo.DonutsPerCop(Cop) <= dbo.MaxPerRank(Cop)) Note the last line -- depending on Rank, each cop can have a different number of donuts they are allowed! Now, not only is the logic in the DB layer, but it can be altered WITHOUT editing DB objects (such as functions or constraints) !! Of course, if the entries in the DonutsPerRank table change, you would need a trigger to ensure you are not causing errors. But it does raise some cool possibilities.

DavidM 2003-11-19	re: Using UDF's for n Cardinality relationships. Thanks Jeff, I have never thought of implementing variable cardinality. That is a very cool approach. The trigger is a small issue as I would imagine the frequency of updates on such a table would low. I just hope one day I get to implement such a thing.

James 2006-04-05	re: Using UDF's for n Cardinality relationships. Great work