CODEWITHSUNDEEP
sql-server
user3448934
user3448934

Reputation:

Check Constraint that references another table

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.

Upvotes: 0

Views: 3489

Answers (1)

RajeshKannan
RajeshKannan

Reputation: 894

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.

Upvotes: 2

Related Questions