one to many mandatory participation agreement at both end using MS SQL

  • Hi, I'm trying to create a one to many mandatory participation agreement at both end using MS SQL.

    a camapign should not exsist without a job(s) and a job should not exsist without a campaign. I'm having trouble writing the check constraint at the one end of Has Jobs relationship. And having trouble getting my head around how this might work, i.e. what table gets update first even if I do get this to work. It seems like a which comes first the chicken or the egg problem. But from what I read it it possible, but how to implement it in MS SQL.

    Help 🙂 really stuck on this one.

    Asta

    USE db1

    CREATE TABLE tblCampaigns

    (Campaign nvarchar(15) NOT NULL,

    PRIMARY KEY (Campaign)

    );

    CREATE TABLE tblJobs

    (JobID int IDENTITY(1,1) NOT NULL,

    Campaign nvarchar(15) NOT NULL,

    ProductIDref nvarchar(10) NOT NULL,

    Batch nvarchar(10) NOT NULL,

    PRIMARY KEY (ProductIDref, Batch),

    CONSTRAINT rel_Jobs_Campaign_Campaign

    FOREIGN KEY (Campaign) REFERENCES tblCampaigns (Campaign)

    ON UPDATE CASCADE,

    );

    CREATE UNIQUE INDEX index_tblJobs_JobID

    ON tblJobs (JobID);

    CREATE INDEX index_tblJobs_Campaign

    ON tblJobs (Campaign);

    /* wokred fine up to here*/

    /* mandatory participation in relationship Has Jobs */

    /*Msg 1046, Level 15, State 1, Line 5

    Subqueries are not allowed in this context. Only scalar expressions are allowed. */

    USE db1

    ALTER TABLE tblCampaigns

    ADD CONSTRAINT mandPartInHasJobs

    CHECK (Campaign IN (SELECT Campaign FROM tblJobs))

    /* mandatory participation in relationship Has Jobs */

    /*Msg 4104, Level 16, State 1, Line 4

    The multi-part identifier "tblJobs.Campaign" could not be bound.. */

    USE db1

    ALTER TABLE tblCampaigns

    ADD CONSTRAINT mandPartInHasJobs

    CHECK (Campaign IN (tblJobs.Campaign))

  • Hmm, I'm pretty sure that you can't have cyclical constraints like that and for exactly the reasons that you are raising.

    It's not possible to create a new Campaign and a new Job simultaneously, so one of them would have to be created first without the other, and that would cause a constraint failure.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Yeah, I'm learning that what I need to do is use INITIALLY DEFERRED key words and set the option wait for commit to on before so that the check constraint is not executed until after the insert into both tables, however I cant find the write syntax for MS SQL.

    any ideas?

    Asta

  • keil.asta (5/10/2009)


    Yeah, I'm learning that what I need to do is use INITIALLY DEFERRED key words and set the option wait for commit to on before so that the check constraint is not executed until after the insert into both tables, however I cant find the write syntax for MS SQL.

    AFAIK, SQL Server does not work like this. And IIRC, even in the DB Products that do, it's usage is problematic and generally discouraged.

    What I would do is to force all DML to these tables to go through stored procedures and then have the stored procedures check these constraints through code, at the end of your transactions.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • that sound interesting, now all I need is a code example and I'll be flying, after 3 days of searching book and the net I cant find one, at not for MS SQL 🙁 but I have to get this sorted for my college project so I will find it.

    Thanks for your help

    Asta

  • mmhhh... do you mean deferrable integrity constraints?

    Yes, you can do that but a deferrable integrity constraint is nothing but a normal integrity constraint that will wait until you commit the transaction to be validated.

    That doesn't help you with the recursiveness you are looking for.

    You have eggs because there are chickens and you have chickens because there are eggs... in my view business rule enforced at the database side would be to ensure there is a Campaign when you insert a Job; may be the application should force the user to create the first Job at the time the Campaign row gets created.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Yes, I agree with Paul's idea. Use normal constraints to ensure that children always have parents, then use the stored procedures code logic to insure that a parent is created with it's first child.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • thank guys, and if I ask really nicely would you have a little example of this chicken and egg problem for MS SQL? or is that pushing it 🙂

  • Here's an example of two of the procedures you will need, to get you started. first, for clarity, here is the DDL that I used:

    CREATE TABLE tblCampaigns(

    Campaign nvarchar(15) NOT NULL,

    PRIMARY KEY (Campaign)

    );

    CREATE TABLE tblJobs(

    JobID int IDENTITY(1,1) NOT NULL,

    Campaign nvarchar(15) NOT NULL,

    ProductIDref nvarchar(10) NOT NULL,

    Batch nvarchar(10) NOT NULL,

    PRIMARY KEY (ProductIDref, Batch),

    CONSTRAINT rel_Jobs_Campaign_Campaign

    FOREIGN KEY (Campaign) REFERENCES tblCampaigns (Campaign)

    --ON UPDATE CASCADE, This prevents ever re-parenting a Job?

    );

    CREATE UNIQUE INDEX index_tblJobs_JobID ON tblJobs (JobID);

    CREATE INDEX index_tblJobs_Campaign ON tblJobs (Campaign);

    /* wokred fine up to here*/

    Now, here is the proc to add a new Campaign:

    --====== Procs to Implement Campaign-Job Relationship

    Create proc spAddNewCampaign(

    @Campaign nvarchar(15),

    @ProductIDref nvarchar(10),

    @Batch nvarchar(10),

    @JobID int OUTPUT

    ) AS

    DECLARE @ErrorSeverity as int,

    @ErrorState as int,

    @ErrorMessage as nvarchar(2048);

    BEGIN Transaction

    BEGIN TRY

    INSERT into tblCampaigns

    Values (@Campaign);

    INSERT into tblJobs (

    Campaign,

    ProductIDref,

    Batch)

    Values (

    @Campaign,

    @ProductIDref,

    @Batch)

    --Return the new JobID to the App

    Select @JobID = SCOPE_IDENTITY()

    END TRY

    BEGIN CATCH

    --save error info

    SELECT

    @ErrorSeverity=ERROR_SEVERITY(),

    @ErrorState=ERROR_STATE(),

    @ErrorMessage=ERROR_MESSAGE();

    ROLLBACK Transaction

    RAISERROR (@ErrorMessage, -- Message text.

    @ErrorSeverity, -- Severity.

    @ErrorState -- State.

    );

    END Catch

    --If we got here, then we're OK

    COMMIT Transaction

    --No need to check that Campaign has a Job, because we just did it.

    GO

    And here is a proc to handle Job deletes:

    Create proc spDeleteCampaignJob(

    @JobID int OUTPUT

    ) AS

    DECLARE @ErrorSeverity as int,

    @ErrorState as int,

    @ErrorMessage as nvarchar(2048);

    DECLARE @Campaign as nvarchar(15);

    BEGIN Transaction

    BEGIN TRY

    --Save the Campaign name

    SELECT @Campaign = Campaign

    From tblJobs

    Where JobId = @JobID;

    --Delete the job

    DELETE From tblJobs

    Where JobId = @JobID;

    --Delete the Campaign too, if no Jobs left

    DELETE From tblCampaigns

    Where Not Exists(

    Select *

    From tbljobs

    Where Campaign = @Campaign

    );

    END TRY

    BEGIN CATCH

    --save error info

    SELECT

    @ErrorSeverity=ERROR_SEVERITY(),

    @ErrorState=ERROR_STATE(),

    @ErrorMessage=ERROR_MESSAGE();

    ROLLBACK Transaction

    RAISERROR (@ErrorMessage, -- Message text.

    @ErrorSeverity, -- Severity.

    @ErrorState -- State.

    );

    END Catch

    --If we got here, then we're OK

    COMMIT Transaction

    GO

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (5/10/2009)


    Yes, I agree with Paul's idea. Use normal constraints to ensure that children always have parents, then use the stored procedures code logic to insure that a parent is created with it's first child.

    Yes that is 100% correct 🙂

  • Barry, Thank you very very much for the code, as soon as I get home I will try it.

    Your a star.

    Asta 🙂

  • Barry Tank you so much your a star, will try it out this evening when I get home 🙂 Asta

  • work like a dream, thank you very much!

    asta 🙂

  • Glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply