mandatory participation at both ends

Last Post 10 May 2009 02:51 PM by keil.asta. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
keil.asta
New Member
New Member

--
10 May 2009 07:01 AM
Hi if you have two tables with a 1:1 relationship and mandatory participation at both ends, how do you implement this in MS SQL 2008

Here is the code I have written but it does not work and I dont understand the error.

Thank you for your help
Asta

CREATE TABLE tblCampaigns
(Campaign nvarchar(15) NOT NULL,
PRIMARY KEY (Campaign),
/* mandatory participation in relationship Has Jobs */
/* Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "tblJobs.Campaign" could not be bound.*/
CONSTRAINT mandPartInHasJobs
CHECK (Campaign IN (tblJobs.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);
keil.asta
New Member
New Member

--
10 May 2009 11:41 AM
Hi Gunneyk

Thank you and that makes sense, so I did what you said and code works but then when I try to add the constraint to the db after its been created I still get the same error messages.

Thanks Asta

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))
keil.asta
New Member
New Member

--
10 May 2009 02:51 PM
In SQLanywhere you can "set option WAIT_FOR_COMMIT = on" and ..... CONSTRAINT abc INITIALLY DEFERRED.....

so that the constraint is not checked until after you have inserted into both tables of a mandatory at both ends situation.

This syntax does no seem to work in MS SQL. I think this is What I need to do to get this to work

Asta
You are not authorized to post a reply.

Acceptable Use Policy