Table Constraints

  • 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

  • Anthony

    You'd need to create a trigger on your Episodes table that would roll back any INSERT operation that didn't comply.

    John

  • 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.

  • 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 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

  • 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

  • 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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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,

    @season,

    @Episode_Number

    )

    RETURN 0

    END

    ELSE

    BEGIN

    RETURN 2

    END

    END

    Anthony

  • 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