Hi
I have got two related tables (tblMembers and tblTickets) that have a 1 to 2 relationship i.e a member can have 1 or 2 tickets, but not more.
How do I enforce this condition in SQL?.
Please help
Hello,
There are several ways to implement this. I assume you have a table similar to tblMemberTicket in which you have a 1-n from tblMemebers and a 1-n from tblTickets...
1. Triggers
2. Check constraints
3. Application login in stored procedures.
If you need enforcement at the schema level, then only options 1 or 2 are applicable.
Cheers,
Rob
|||Thanks Rob, I've solved it with a check constraint.
(Isn't that triggers are executed after the INSERT?..... i'm a newbie)
|||Hi GM,
An INSTEAD OF INSERT trigger for example, fires when any action attempts an insert - not "after" the data has been inserted (so to speak).
Cheers,
Rob
|||How did you do this with a check constraint? The only way I can think is to it in a check constraint is to have a domain of two value:
columnName tinyint check (column between 1 and 2)
would do it, but it would seem clumsy to work with.
You couldn't really do it in a check constraint that uses a user-defined function easily since the value isn't in the table yet, you would have to see if two already exist.
Best to use an AFTER trigger for that. Basically you check once the data in in the table (inside your transaction of course) to see if an invalid situation exists. Then you rollback as needed:
create table cardinalityTest
(
cardinalityTest int identity(1,1),
groupColumn char(10),
value int
) --I would expect a natural key to be available in a real table
go
create trigger cardinalityTest$insertTrigger
on cardinalityTest
after insert, update
as
begin
begin try
if exists (select groupColumn
from cardinalityTest
--only check groupColumn values that have changed
where exists ( select *
from inserted
where inserted.groupColumn = cardinalityTest.groupColumn)
group by groupColumn
having count(*) > 2 --two is allowed
)
raiserror ('Greater than 2 values for a groupColumn value',16,1)
end try
begin catch
if @.@.trancount > 1
rollback transaction
--you can do something more interesting
declare @.message varchar(4000)
set @.message = error_message()
raiserror (@.message, 16, 1)
end catch
end
go
insert into cardinalityTest(groupColumn, value)
select 'first',1
insert into cardinalityTest(groupColumn, value)
select 'first',1
insert into cardinalityTest(groupColumn, value)
select 'first',2
Msg 50000, Level 16, State 1, Procedure cardinalityTest$insertTrigger, Line 26
Greater than 2 values for a groupColumn value
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
insert into cardinalityTest(groupColumn, value)
select 'second',1
union all
select 'second',1
insert into cardinalityTest(groupColumn, value)
select 'nope',1
union all
select 'nope',1
union all
select 'nope',1
Msg 50000, Level 16, State 1, Procedure cardinalityTest$insertTrigger, Line 26
Greater than 2 values for a groupColumn value
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
insert into cardinalityTest(groupColumn, value)
select 'third',1
union all
select 'third',1
union all
select 'fourth',1
union all
select 'fourth',1
insert into cardinalityTest(groupColumn, value)
select 'fifth',1
union all
select 'fifth',1
union all
select 'fifth',1
union all
select 'sixth',1
Msg 50000, Level 16, State 1, Procedure cardinalityTest$insertTrigger, Line 26
Greater than 2 values for a groupColumn value
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
|||
CHECK constraints are evaluated at the end of the DML action so you could use a UDF like:
create function CT2 ()
returns bit
as
begin
return(select top 1 1 from CardinalityTest group by groupColumn having count(*) > 2)
end
go
-- Table-level CHECK constraint
alter table CardinalityTest
add check ( dbo.CT2() is null )
go
|||
Wow. I sure had that wrong. I was thinking that checks happened before the data was in the table.
Thanks for the info.
|||Hi
Does this apply to SQL Server 2000 too ?
Because right now, I'm experiencing quite different behavior....
I have temporal table where each row hase ActiveFrom, ActiveTo column.
There is "primary key", but there can be more than one row with same primary key value as long as rows has different, not overlaping time interval specified by ActiveFrom and ActiveTo columns...
There is no way to create real primary key here so i'm trying to create table level constraint using UDF which checks this.....
My function:
create function TestFinancialIndicatorBusinessKey()
returns int
as
begin
declare @.retVal int
SELECT @.retVal = ISNULL(COUNT(*), 0)
FROM FinancialIndicator AS A
INNER JOIN FinancialIndicator AS B
ON (A.Id != B.Id) AND (A.IdFSFormat = B.IdFSFormat) AND (A.IdFIType = B.IdFIType) AND (A.MainSegment = B.MainSegment)
WHERE dbo.IsIntervalIntersectionNonEmpty(A.ActiveFrom, A.ActiveTo, B.ActiveFrom, B.ActiveTo) > 0
PRINT ISNULL(@.retVal, 0)
return @.retVal
end
GO
(Id is syntetic primary key - identity, (IdFSFormat,IdFIType, MainSegment) is "primary key"
I've created constraint like this:
ALTER TABLE dbo.FinancialIndicator
WITH CHECK ADD CONSTRAINT CHK_BUSINESS_KEY
CHECK (dbo.TestFinancialIndicatorBusinessKey() = 0);
Everything seems fine, but doesn't work ! Firts update to table, which break this rule is executed without problem. Next update which should repair the incorect row fails....
It seems like this table level constraint is executed only before my update....
Any idea
Thanx, Michal
