6

I have a simple database and I'm making a relationship between two tables. So I have this 3rd table where they are linked. Does the third table require a primary key? Because all I need in it is to link the two tables together and an extra column for something else.

Kate Gregory
  • 18,808
  • 8
  • 56
  • 85
Tom
  • 421
  • 3
  • 6
  • 12
  • You have to have a primary key, however the two foreign keys could form a compound primary key. – Lyuben Todorov Nov 22 '12 at 18:27
  • You generally don't "have to have a primary key" - but you ought to have one! (@Lyuben Todorov) – DRobinson Nov 22 '12 at 18:36
  • I disagree, in-fact here was a big lovely argument about it. @DRobinson [Yes we have to have primary keys](http://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key) unless we fall under these special special cases. – Lyuben Todorov Nov 22 '12 at 18:41
  • It's English semantics. They're saying you "should" always, you're saying you "have to". It's possible to not have one, meaning you don't "have to". – DRobinson Nov 22 '12 at 18:45
  • @DRobinson you cant guarantee 1NF without a primary key, so then you can create a table but not a relation. – Lyuben Todorov Nov 22 '12 at 18:53
  • Yes, which is exactly why I said "you ought to" without any clauses - in almost every case a primary key is a good thing to have. It's also why I said it was English semantics - I was merely pointing out that it is strictly possible, even if it isn't a good idea. – DRobinson Nov 22 '12 at 19:11
  • Foreign keys are not "NOT NULL" by default. By adding a primary key constraint on the two FK fields, you inherently impose a NOT NULL constraint on them (a PK element *cannot* be NULL). And you enforce *every combination* of their values to be unique, too. – wildplasser Nov 22 '12 at 19:30

2 Answers2

4

The kind of table you're describing is sometimes called a join table. You would create a primary key across the columns you're joining, both for uniqueness and because it's more likely to be indexed for better performance.

C. K. Young
  • 219,335
  • 46
  • 382
  • 435
  • Well of course the foreign keys are individually unique (since they're supposed to be primary keys in their respective tables), but a uniqueness constraint across the two columns means that you don't have duplicate rows in your join table. – C. K. Young Nov 22 '12 at 18:31
  • Well this table joins lets say train routes, with companies that run them. This tabl;e suppose to show the proportion of each company and how many of the trains they run. for example Route A - runned by compaby X where it runs 50% of the trains. If you look at this here is the relation http://stackoverflow.com/questions/13500200/creating-relational-database – Tom Nov 22 '12 at 18:32
  • 2
    You certainly ought to have a key unless you intend to permit duplicate rows. For sound reasons of data integrity and simple usability it isn't usually a good idea to permit duplicate rows in a table! A uniqueness constraint is just another name for a key constraint (assuming the columns in question are non-nullable because otherwise uniqueness wouldn't necessarily be enforcible anyway). – nvogel Nov 22 '12 at 19:29
  • 1
    Declaring a primary key consisting of the columns that make up the two foreign keys will be generally to your advantage. It will enforce uniqueness. And it will cause an index to be built that may speed up your three way joins. – Walter Mitty Nov 23 '12 at 03:27
  • Fine, I've edited my post to say that a primary key across the joined columns is standard practice. – C. K. Young Nov 23 '12 at 05:54
4

What you are describing is a Junction Table. Yes, you can make one without a Primary Key (or, another option is a Compound Primary Key - making the two references a unique pair, and using that as the unique identifying key - but even this isn't necessary (note: just because it "isn't necessary" doesn't mean it isn't "good practice"; it wouldn't generally be a good idea to have several rows with the exact same contents)).

Using those terms, you should be able to find the answers you need for implementation.

DRobinson
  • 4,441
  • 22
  • 31