Database Schema Question

Question

If I use the primary key of a table as the primary key of another table is it still a foreign key?

e.g.

Two tables albums and special offers

AlbumId is the primary key in both

How do I represent this relation using primary key foreign key notation?

Answer 1

Pablo Santa Cruz is right - yes, you're allowed to do this. However, philosophically, it's only meaningful if there really is a one-to-one relationship - all albums have one and only one special offer, and all special offers have one and only one album.

Guessing from your problem domain, that's not the case - some albums have no special offers, some have 1, some have many.

If that is indeed true, bw_üezi is right - create a one-to-many relationship.

create table albums (
    album_id integer primary key,
    -- other fields...
);

create table special_offers (
    special_offer_id integer primary key,
    album_id integer foreign key references albums(album_id),
    -- other fields...
);

Answer 2

if e.g you have several special offers for the same album AlbumId is no longer unique in the special offers table.
I would think about adding a SpecialOfferId and design a one-to-many relation.

Answer 3

Yes, it's still a primary key. It's usually called a one-to-one relation.

You can do something like:

create table albums (
    album_id integer primary key,
    -- other fields...
);

create table special_offers (
    album_id integer primary key references albums(album_id),
    -- other fields...
);