Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Can a column be a PK and FK and is that ok to do?

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-10-19 : 16:06:06
I have 2 tables (tblItems and tblNormalItems) which are a SuperType/SubType relationshipt (like a Person to Employe table). tblItems contains the common basic info of all items and tblNormalItems will contain only data unique to normal items. Is it ok for tblNormalItems to have a column called ItemID wich would be a PK for tblNormalItems but allso be a FK to tblItems (and can that be done by just two normal CONSTRANT calls)? Here is the code for tblItems:

CREATE TABLE dbo.tblItems
(
ItemID bigint IDENTITY(0,1) NOT NULL,
SectionID smallint NOT NULL,
ItemName varchar(250) NOT NULL,
ItemType smallint NOT NULL,
ItemDescription varchar(MAX) NOT NULL,
ItemImageID int NULL,
CONSTRAINT PK_Items_ItemID PRIMARY KEY CLUSTERED (ItemID ASC),
CONSTRAINT FK_Items_SectionID FOREIGN KEY (SectionID) REFERENCES dbo.tblSectionInfo (SectionID),
CONSTRAINT FK_Items_ItemType FOREIGN KEY (ItemType) REFERENCES dbo.tblItemTypes (ItemTypeID),
CONSTRAINT FK_Items_ItemImageID FOREIGN KEY (ItemImageID) REFERENCES dbo.tblFiles (FileID)
)


--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-19 : 17:24:16
Yes, that's perfectly fine and is the only way to guarantee a 1:1 cardinality between those tables.
Go to Top of Page
   

- Advertisement -