0

Say I have a junction table to resolve a many to many relationship I have between two tables. My junction table also has its own 'Method' column to describe the relationship. Junction Table Example

Normally, I would make a composite primary key of [a], [b] and [method] (the method needs to be part of what makes a row unique), but my problem is that [method] field can be NULL. Therefore I cannot add it to the primary key.

So what I've done is create a unique index:

ALTER TABLE A_B ADD UNIQUE INDEX `Unique` (`a`, `b`, `method`);

The table has no primary key. Is this an okay thing to do or do I need to do something differently?

Cody
  • 2,451
  • 2
  • 20
  • 19
  • 1
    A primary key in your junction table may not be necessary, but things I would consider include: (1) a unique index or primary key prevents duplication (redundancy) of data; (2) a primary key is helpful when updating/deleting rows because it removes ambiguity about the row being targeted. FWIW even though you can include a nullable column in a unique index, MySQL does not enforce key uniqueness when part of the key is NULL i.e. you can only have one 'a','b','x' row, but any number of 'a','b',NULL rows. – Zenilogix Jun 19 '13 at 14:53

1 Answers1

1

Using a primary key is not the only way to ensure records uniqueness. There is a unique constraint you can implement to accomplish what is needed.

http://www.w3schools.com/sql/sql_unique.asp

lamilambkin
  • 117
  • 1
  • 1
  • 9
  • I am forcing uniqueness through the unique index I added, so I should be good there. From a DB design perspective, it is okay to not have a key on a junction table? – Cody Jun 18 '13 at 18:21
  • 1
    Although uniqueness can be enforced through a constraint without an index, using a unique index is usually the better choice (assuming more than a "few" rows). I would only omit indexes on foreign key columns where the table is guaranteed to only ever have just a handful of rows. – Zenilogix Jun 18 '13 at 19:11