Enforce constraint?

Question

Enforce constraint?

Jules Bonnot

SSCertifiable

Points: 5488
More actions
July 6, 2010 at 4:37 am

#387278

create table test_const
(
id int primary key,
fk_id int,
bit_1 bit
)
insert test_const
select 1, 1, 0 union all
select 2, 1, 0 union all
select 3, 1, 0 union all
select 4, 1, 1 union all
select 5, 2, 0 union all
select 6, 2, 0 union all
select 7, 2, 0 union all
select 8, 2, 0
i need to code a constraint that ensures each fk_id group contains at least one true value in the bit_1 field.
so fk_id group 1 is valid but fk_id group 2 is not.
Can i do this without using a trigger?
what would be the most efficient method?
thanks for all help.
Jules
www.sql-library.com[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

Jules you can do it with a check constraint and function, but that would force a logical requirement: the first fk_id in the series

must have it's bit_1 with the value = 1, or the statement would fail.

here's your example with what i'm referring to:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chkgrp". The conflict occurred in database "tempdb", table "dbo.test_const", column 'fk_id'.
The statement has been terminated.

your code,slightly modified:

create table test_const

(

id int primary key,

fk_id int,

bit_1 bit

)

GO

create function grpsum(@fk_id int)

returns int

as

begin

return (select sum(convert(integer,bit_1)) from dbo.test_const where fk_id=@fk_id)

end

GO

alter table dbo.test_const add constraint chkgrp check (dbo.grpsum(fk_id) >=1)

GO

insert test_const

select 1, 1, 0 union all

select 2, 1, 0 union all

select 3, 1, 0 union all

select 4, 1, 1 union all

select 5, 2, 0 union all

select 6, 2, 0 union all

select 7, 2, 0 union all

select 8, 2, 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

nice, thanks. being able to put a function in a check contraint is very useful...

www.sql-library.com[/url]