Help Please! Check constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help Please! Check constraint

Author

Topic

omGac0W
Starting Member

1 Post

Posted - 2012-02-29 : 22:07:17

Hi.

So I have two tables:

ce_events

.
.
.
event_exp_attendance
event_venue_id

ce_venues

venue_id
venue_capacity

One table is ce_events, the other is ce_venues. I want to create an alter table that adds a check constraint to the ce_events table to make sure that event_exp_attendance is between 0 and the venue_capacity with that event_venue_id.


alter table ce_events
	add 
		constraint ck_event_exp_attendance_valid
			check(event_exp_attendance between 0 and ???)

How do I check the venue_capacity in the other table? I tried to use a sub-select statement but it didn't like that and will only accept scalar values. Any help would be great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 22:23:08

you should be using a udf for that and link it to the check constraint

make udf like


CREATE FUNCTION IsAttendenceValid
(
@Attendence int,
@event_venue_id int
)
RETURNS bit
AS
BEGIN

DECLARE @Valid bit

SELECT @Valid= CASE WHEN @Attendence BETWEEN 0 AND  venue_capacity THEN 1 ELSE 0 END
FROM ce_venues
WHERE venue_id = @event_venue_id


RETURN @Valid
END



then use it like

alter table ce_events
	add 
		constraint ck_event_exp_attendance_valid
			check(dbo.IsAttendenceValid(event_exp_attendance,event_venue_id)=1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources