CODEWITHSUNDEEP
sqlsql-serversql-server-2008
codeGEN
codeGEN

Reputation: 714

Relationship between composite keyed table & non primary key table

I have two tables named ORDER_HEAD and ORDER_DETAIL, following is their structure.

ORDER_HEAD:

ORD_ID (PK)
ORD_TYPE (PK)
ORD_DATE
ORD_NET_TOTAL

ORDER_DETAIL:

ODD_ID
ODD_LINE_NO
ODD_PRODUCT_ID
ODD_QTY

I want to form a relationship between the ORD_ID in ORDER_HEAD & ODD_ID in ORDER_DETAIL table. But SQL Server shows an error message:

primary key or unique constraint must be defined for table before it can participate in a relationship

Why am I getting this error ? Is there a way to perform a join between these two columns or is there a problem in my db design ?

Upvotes: 0

Views: 114

Answers (2)

user2941651
user2941651

Reputation:

UPDATE

After rethinking the problem I think you should achieve your goal by adding reference column ODD_TYPE (like user @NoDisplayName stated) and creating composite PK for your table ORDER_DETAIL consisting of 3 columns (ODD_ID, ODD_TYPE, ODD_LINE_NO), then it would be:

ORDER_DETAIL
============
ODD_ID          (PK)
ODD_TYPE        (PK)
ODD_LINE_NO     (PK)
ODD_PRODUCT_ID
ODD_QTY

in SQL it could be:

ALTER TABLE ORDER_DETAIL ADD CONSTRAINT PK_Order_Detail PRIMARY KEY NONCLUSTERED (ODD_ID, ODD_TYPE, ODD_LINE_NO)

Then, in ORDER_DETAIL table for specific pair (ODD_ID, ODD_TYPE) you would have records being its order lines.

I think that after removing the previous PK, adding the column and setting the above key (even in visual editor) you shouldn't have problems when creating FK between the two tables and mapping the proper columns together.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

If you want to create a relationship from Composite primary key then any reference should also include all the column's in Composite primary key

Schema of ORDER_DETAIL should be

ORDER_DETAIL
============
ODD_ID (Fk)
ORD_TYPE(Fk) -- you need to add this column
ODD_LINE_NO
ODD_PRODUCT_ID
ODD_QTY

Create Foreign key like this.

ALTER TABLE ORDER_DETAIL
  ADD CONSTRAINT FK_ORDER_DETAIL
  FOREIGN KEY(ODD_ID, ORD_TYPE) REFERENCES ORDER_HEAD(ODD_ID, ORD_TYPE)

Upvotes: 2

Related Questions