Is this lookup (association) table correct? (many to many relationship)

Question

CREATE TABLE jokecategory (
  jokeid INT NOT NULL,
  categoryid INT NOT NULL,
  PRIMARY KEY (jokeid, categoryid)
) DEFAULT CHARACTER SET utf8;

especially PRIMARY KEY (jokeid, categoryid)? or is there a better way to write this?

Thank you in advance;-)

Answer 1

Yes it is a perfectly good table and primary key (I might call this an "association" table, but I would not call it a "lookup" table).

Some people (not I) would insist on having a surrogate key column jokecategoryid as the primary key; if you do that you still need a UNIQUE constraint on (jokeid, categoryid) to enforce the business rule.

Answer 2

I would expect the table to have two foreign keys e.g.

CREATE TABLE jokecategory (
  jokeid INT NOT NULL REFERENCES joke (jokeid), 
  categoryid INT NOT NULL REFERENCES category (categoryid), 
  PRIMARY KEY (jokeid, categoryid)
) DEFAULT CHARACTER SET utf8;

Answer 3

Yes, it is a perfectly good table, as Tony has already pointed out. But I see no need to create it as a regular heap table with a primary key index. That's two storage structures. Just the index structure would do, so my advice is to create this table as an index organized table.

Here is a link to the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/indexiot.htm#CNCPT911

Regards, Rob.