When creating a database what happens if one table only contains 2 primary keys from other tables, i'm assuming they're both foreign keys. Does there have to be a primary key in the table?
4 Answers
A link table with just two foreign keys and nothing else (no surrogate key added to make a simple meaningless primary key) will usually be constrained to be unique (otherwise you will not be able to distinguish duplicates - and this is also a violation of normal form) so you will often just go ahead and have those two keys together as a composite make up the primary key (PKs have to be unique by definition, and they form a natural choice for the PK of such a link table). The order of those columns in the primary key is usually determined by the most frequent order of search - i.e. personid, accountid might have personid first in a composite primary key on personid, accountid.
3 Comments
Bill Karwin
+1 Yes, you're right a primary key is a constraint on one or more columns. It is not a column, as the other answers assume.
Dave Newton
While I agree, none of that is required, which is how I read the question.
Cade Roux
@Dave Newton While it isn't required to have a primary key, maintenance of such a table would, in almost all likelihood I can imagine, actually strongly assume (but yes not require, although being unable to distinguish duplicates easily is rather pointless) a unique key, and given there are only two columns, and they are both foreign keys and unlikely to be NULL, I would go ahead and make it (the composite) the primary key and kill all the birds with one stone.
No; tables don't have to have primary keys.
They often (usually?) don't when they're linking/mapping tables like this.
Comments
A Table doesn't necessarily need to have a primary key associated. It i completely valid as follows
Table_Album
pkey | name 1 | name1 2 | name2 Table_Song
pkey | name 1 | song1 2 | song2 3 | song3 You can then have a table which states
Table_Album_Song_Map
id | Album | Song # Here id is just row number and not primary key 1 | name1 | song1 2 | name1 | song2 3 | name2 | song3 Hope that helps
Comments
When you have an associative entity or table, it is good practice to create a composite primary key consisting of the keys of both parents. In the example, there would be a uniqueness constraint on the Album-Song-Map, on the composite of Album and Song. If you do not make these the primary key and make the combination unique, then you can have duplicates in Album-Song-Map. The composite key of Album-Song-Map is the key of both parents, but individually they are foreign keys back to their parent, Album and Song respectively. In my experience, the associative entity is usually not just for mapping but also contains some business attributes. For example, say the song has a different Duration (playing time) on one album than another. This attribute would have to go in Album-Song-Map. I use a lot of music for dancing, and often the duration of a song is different on different albums.
