How to implement numeric multiplicities like 1..3 in SQL Server

Question

I haven't been able to figure out how to implement unconventional multiplicities such as 1..3 or 5..15 in SQL Server. I've can implement 0..1, 1..1, 0..* and 1..*. Now I to enforce a constraint a Trio entity must be associated with at least one Singer entity in order to add a record to the TRIOS table, but no Trio record may be associated with more than 3 singers.

Can you help?
Thanks, Bob

0..* multiplicity isn't so hard. You could do it by, for example, creating a function like `CREATE FUNCTION fn_checkSingerCount(@i INT) RETURNS INT AS BEGIN DECLARE @out INT; SELECT @out = COUNT(*) FROM myTable WHERE singerID = @i; RETURN @out; END` then adding a check constraint to the table like `ALTER TABLE myTable ADD CONSTRAINT check_totalSingers CHECK (dbo.fn_checkSingerCount(singerID) <= 3)` or something like that... I'm not sure how 5..15 would work, though. Would you need to insert at least 5 records or something? — ZLK, Sep 21 '16 at 03:43

score 0 · Answer 1 · answered Sep 21 '16 at 05:13

You can use a check constraint like this to manage the maximum number of records.

create table trios (trio_id int, person_id int, unique clustered (trio_id, person_id));
go

create function dbo.trio_check (@trio_id int)
returns int
as
begin
    declare @result int;
    select @result = count(1) from trios where trio_id = @trio_id;
    return @result;
end;
go

alter table trios add constraint trio_check1 check (dbo.trio_check(trio_id) <=3);
go

How to implement numeric multiplicities like 1..3 in SQL Server

1 Answers1