Check Constraint that references another table

Question

Have a situation 2 different tables

 CREATE TABLE albums(
            album_id int identity (10,5) not null,
            album_title varchar (40) not null,
            album_itunes_price decimal DEFAULT 12.99 not null,
            album_group_id int not null,
            album_copies_sold int DEFAULT 0 null
            )
go



 CREATE TABLE SONGS
 (song_id int identity (5,5) not null,           
 song_title varchar (50) not null,         
 song_group_id int not null,     
 song_album_id int null,
 song_time time not null,
 song_itunes_cost money not null )

 GO

 ALTER TABLE songs
ADD 

CONSTRAINT pk_song_id
PRIMARY KEY (song_id),

CONSTRAINT fk_song_album_id
FOREIGN KEY (song_album_id)
REFERENCES albums(album_id),

CONSTRAINT fk_song_group_id
FOREIGN KEY (song_group_id)
REFERENCES groups(group_id)

go

create a check constraint on songs table called ck_songs_itunes_cost The songs can be free but they can never be more than the album_itunes_price.

How do i create this constraint been working on it for 12 hours nothing is working.

Possible duplicate of http://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint — shree.pat18, Mar 22 '14 at 06:48

RajeshKannan · Accepted Answer · 2014-03-22T06:53:39.193

2

I think the below is what you are expecting:

create a function that returns the values from album table:

Create function [dbo].[MaxValue]()
returns decimal
as
begin
   declare @retval int
   select @retval=MAX(album_itunes_price) from dbo.albums
   return @retval
end;

Then create check constraint in songs which can get the value from albums table, because we can't use subquery in check constraint.

alter table dbo.songs
add constraint ck_songs_itunes_cost 
check (songscolumn < dbo.MaxValue())

Make use of it based on your need.

edited Mar 22 '14 at 06:53

answered Mar 22 '14 at 06:48

RajeshKannan

894
2
16
32

Check constraints with user defined functions are not reliable, see for example [this blog post](http://benchmarkitconsulting.com/colin-stasiuk/2011/08/08/check-constraints-that-call-functions/) or [this MSDN page](http://msdn.microsoft.com/en-us/library/ms188258%28v=sql.105%29.aspx). – Andomar Mar 22 '14 at 10:25
OMG THANKS ! IT WORKED... WE CREATED A FUNCTION SIMILAR BUT COULDN'T GET THE CONSTRAINT TO CALL IT ... – Mar 22 '14 at 22:42

Check Constraint that references another table

1 Answers1