SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 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?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

424 Posts

Posted - 10/19/2012 :  16:06:06  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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 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.

Most Valuable Yak

15723 Posts

Posted - 10/19/2012 :  17:24:16  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yes, that's perfectly fine and is the only way to guarantee a 1:1 cardinality between those tables.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000