April 28, 2008 at 9:48 am
Hi Guys
I am fairly new the SQL scene and I am just getting my head around a few things at the moment.
I have created a dummy database detailing some television shows.
I want to place a constraint on my Episodes table which will not allow a record to be inserted if that specific show is not in that season.
Table structure
TAB - TVShow
TVShowID - PK, int
TVShow - varchar 100
Current_Season - int
TAB - Episodes
EpisodeID - PK, int
TVShowID - FK to TVShow.TVShowID
Episode_Name - varchar 100
Season - int
Episode_Number - int
I want to link TVShow.Current_Season to Episode.Season but I am unsure on how to go about this as I want it to error if I try to insert a record for season 8 in the Episodes table, if the TVShow.Current_Season is not = 8.
Hope this makes sence?
Thanks
Anthony
April 28, 2008 at 9:54 am
Anthony
You'd need to create a trigger on your Episodes table that would roll back any INSERT operation that didn't comply.
John
April 28, 2008 at 10:09 am
You can do this if you put your check constraint into a UDF. Pass in a show and a season number and return a boolean and then use the UDF in your check constraint.
April 28, 2008 at 10:10 am
you can create a proc for insert and usea IF exists
Example
CREATE PROC HND_INSEPISODES
@EpisodeID int,
@TVShowID int,
@Episode_Name varchar (100),
@season int,
@Episode_Number int
AS
BEGIN
IF EXISTS (select *from TVShow where TVShowID = @TVShowID)
Insert into Episodes (@EpisodeID ,@TVShowID,@Episode_Name,@Season ,@Episode_Number )
ELSE
PRINT 'TV SHOW DOSENT EXISTS'
END
anthony.green (4/28/2008)
Hi GuysI am fairly new the SQL scene and I am just getting my head around a few things at the moment.
I have created a dummy database detailing some television shows.
I want to place a constraint on my Episodes table which will not allow a record to be inserted if that specific show is not in that season.
Table structure
TAB - TVShow
TVShowID - PK, int
TVShow - varchar 100
Current_Season - int
TAB - Episodes
EpisodeID - PK, int
TVShowID - FK to TVShow.TVShowID
Episode_Name - varchar 100
Season - int
Episode_Number - int
I want to link TVShow.Current_Season to Episode.Season but I am unsure on how to go about this as I want it to error if I try to insert a record for season 8 in the Episodes table, if the TVShow.Current_Season is not = 8.
Hope this makes sence?
Thanks
Anthony
April 28, 2008 at 10:50 am
If you really want to do that, then have the insert proc use the current season only. That's a simple select statement for that column.
But I have to ask, are you sure you're never going to want to add back in the history of a show? I don't know the purpose of the database, but are you really never going to allow anyone using it to go back and add in things like "Seinfeld, Season 1"? If not, then I'd just make it part of the insert proc. If so, then you'll need to manage that a little more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 28, 2008 at 12:08 pm
Another option -- You can create a stored procedure incorporating two input parameters to execute something similiar to the following
DECLARE @News INT -- new season
DECLARE @TVShowID INT -- existing value in table TVShow
DECLARE @GotIt INT
SET @TVShowID = 4 --value to be passed to SP
SET @News = 3 --value to be passed to SP
SET @GotIt = 0
SET @GotIt = (SELECT TVShow.Current_Season FROM TVShow
WHERE TVShow.TVShowID = @TVShowID)
IF @GotIt = @News
BEGIN
--PRINT 'A match' used for testing
UPDATE Episodes SET Season = @News
WHERE Episodes.TVshowID = @TvShowID
END
ELSE
--PRINT 'No match' used for testing
-- should raise a user defined error here
April 29, 2008 at 9:40 am
Guys
Created a SPROC usp_InsertEpisode to which I will pass values, seems to work, but any tweaks you can think of
CREATE PROCEDURE usp_InsertEpisode
(
@TVShowIDINT,
@Episode_NameVARCHAR(100),
@seasonINT,
@Episode_NumberTINYINT
)
AS
BEGIN
DECLARE @CurrentSeason INT
SET @CurrentSeason = ISNULL((
SELECT Current_Season FROM TVShow WHERE TVShowID = @TVShowID
),0)
IF @season <= @CurrentSeason
BEGIN
INSERT INTO Episodes
(
TVShowID,
Episode_Name,
Season,
Episode_Number
)
VALUES
(
@TVShowID,
@Episode_Name,
@Episode_Number
)
RETURN 0
END
ELSE
BEGIN
RETURN 2
END
END
Anthony
April 29, 2008 at 11:29 am
That looks like it should work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply