SQL Server - Noob Relationships Question

Question

I have two tables:

CREATE TABLE InmarsatZenith.dbo.ClientJob
(JobRef int PRIMARY KEY NOT NULL,
IntRef uniqueidentifier,
CopyDeadline datetime,
PublicationDate datetime,
Repeat bit,
BusinessType nvarchar(25),
Sector nvarchar(30),
Lang nvarchar(15),
Format nvarchar(25),
CreativeRotation nvarchar(50),
TipinType nvarchar(25))

and

CREATE TABLE InmarsatZenith.dbo.Comment
(ID int identity PRIMARY KEY NOT NULL,
IntRef uniqueidentifier,
CreatedDate datetime,
ModifiedDate datetime,
CreatedBy nvarchar(25),
ModifiedBy nvarchar(25),
Comment text)

I want to be able to create multiple "Comments" that all link back to the ClientJob table based on the "IntRef" field. So essentially everytime a comment is made, the INTREF of the ClientJob is specified and then when I pull back all of the details from the ClientJob table, all of the comments come with it to. I have tried setting up a relationship between the two tables, however SQL Server states that the Primary Key is not The IntRef field in the ClientJob table and thus I cannot link the Comments table to it. Is there a way that I can have multiple Primary keys in the ClientJob table?! Or another more efficient way of doing this altogether. The relationship is "1 ClientJob has MANY Comments".

Help greatly appreciated,

Kind regards.

Answer 1

For foreign key, you need Primay Key or unique index. Put unique index to IntRef in InmarsatZenith.dbo.ClientJob table

Answer 2

You can only ever have one primary key on any table. But you can also do a foreign key relationship to a unique index.

Assuming your reference is "One ClientJob can have multiple comments, but each comments only ever belongs to exactly one ClientJob", then these two steps will create the necessary bits for you:

1) Put a unique index on "IntRef" in your "ClientJob" table:

CREATE UNIQUE INDEX UIX_IntRef 
   ON dbo.ClientJob(IntRef)

2) Put a foreign key relationship from Comments to ClientJob:

ALTER TABLE dbo.Comments
  ADD CONSTRAINT FK_Comment_ClientJob
  FOREIGN KEY (IntRef)  REFERENCES dbo.ClientJob(IntRef)

That should do it!

Marc