SQL Server Magazine

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);

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

I just realized what I think you are trying to do but I don't understand why. You created a FK constraint on the Campaign between the tblJobs and tblCampaigns so you shouldn't need anythign else. If you wrap the 2 inserts in a transaction you will be guaranteed to either have both or none so the constraint is valid. You can put a DELETE trigger to stop any deletes from Campaign table if there are Jobs with that Campaign.

And please don't muti post. There aren't that many forums and it will be seen regardless.

-------------------------
Andrew J. Kelly