CODEWITHSUNDEEP
database-designprimary-keynormalization
MGZero
MGZero

Reputation: 5963

Primary and foreign keys

Simple question, can't seem to find an answer on Google. I'm normalizing a database and I'm converting one of the tables to 2nd Normal Form. Question is: can the primary key of a table also be a foreign key in that same table?

If it makes it easier to understand, I'm trying to eliminate the partial dependencies in the table (but that was probably obvious since I mentioned 2nd Normal Form).

I have the table JobItem, which looks like this:

Job No (PK), Item Code (PK), PO Num, Item Description, Item Type, Vendor, Job Name

Job Name is partially dependent on the Job No component of the key. Item Description, Item Type and Vendor are dependent on the Item Code component. PO Num is dependent on the entire key.

What I'm asking is once I strip out the partial dependencies into their own tables, will the components of my key also become foriegn keys to reference the new tables?

EDIT: Fleshed out the example to be a bit more relevant.

Upvotes: 1

Views: 289

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

You'll get better answers if you post the DDL of the table. (Post the CREATE TABLE script.) As a rule of thumb, always post DDL and INSERT statements for sample data.

Question is: can the primary key of a table also be a foreign key in that same table?

Yes, but that doesn't descibe your situation. Your primary key is in one table (Table B), and your foreign key is in a different table (Table A).

A self-referencing foreign key, in which a foreign key in Table A references a candidate key that's also in Table A, is relatively uncommon. That is, foreign keys that reference other tables are much, much more common than foreign keys that reference their own table.

In fact, you can find quite a few designs on SO--and not just on SO--that use self-referencing foreign keys when they really shouldn't. That's another good reason to post DDL.

Later

What I'm asking is once I strip out the partial dependencies into their own tables, will the components of my key also become foriegn keys to reference the new tables?

Yes. There are rare instances where the columns of a multi-column candidate key actually need to reference different tables entirely. But in your case, it looks like you're right--the columns of your key should reference the primary keys in the new tables.

Upvotes: 1

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Something like this?

enter image description here

Upvotes: 2

Related Questions