May 10, 2009 at 12:55 pm
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))
May 10, 2009 at 4:28 pm
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]
May 10, 2009 at 4:34 pm
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
May 10, 2009 at 4:43 pm
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]
May 10, 2009 at 4:52 pm
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
May 10, 2009 at 4:54 pm
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.May 10, 2009 at 5:14 pm
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]
May 10, 2009 at 5:18 pm
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 🙂
May 10, 2009 at 5:57 pm
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]
May 11, 2009 at 5:09 am
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 🙂
May 11, 2009 at 5:13 am
Barry, Thank you very very much for the code, as soon as I get home I will try it.
Your a star.
Asta 🙂
May 11, 2009 at 5:14 am
Barry Tank you so much your a star, will try it out this evening when I get home 🙂 Asta
May 11, 2009 at 9:30 am
work like a dream, thank you very much!
asta 🙂
May 11, 2009 at 10:27 am
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