Using UDF's for n Cardinality relationships.

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

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!

Print | posted on Tuesday, November 18, 2003 4:03 PM

Feedback

# re: Using UDF's for n Cardinality relationships.

left by Jeff at 11/19/2003 4:04 AM Gravatar
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.

# re: Using UDF's for n Cardinality relationships.

left by DavidM at 11/19/2003 8:00 AM Gravatar
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.

# re: Using UDF's for n Cardinality relationships.

left by James at 4/5/2006 1:07 PM Gravatar
Great work
Comments have been closed on this topic.