mysql ( innodb )foreign key constraints problems

Question

Hello

I am running into a couple of issues while trying to generate foreign keys for my tables in MySql(Innodb). Can you please help me with them ?

Referenced tables :

*create table entity
{
  PID INT(20) auto inc not null,
  ENTITYID INT(20) not null,
  details VARCHAR(100) not null,
  primary key(PID,ENTITYID)
}
create table user
{
 USERID int(20) auto inc not null,
 NAME VARCHAR(45) not null,
 joindate DATETIME not null,
 location VARCHAR(100) not null,
 primary key(USERID,NAME) not null
 }*

Referencing table:
*create table C
{
  ENTITYID INT(20) not null,
  NAME VARCHAR(45) not null,  
  foreign key ENTITYID_C constraint ENTITYID references entity(ENTITYID) on delete cascade,
  foreign key name_C constraint NAME references user(NAME) on delete cascade,
  primary key(ENTITYID,NAME)  
}*

I need 2 foreign keys in table C because the entry in C must be deleted when either the corresponding entity or corresponding user is deleted.

When i try to create table C I run into a error: ERROR 1005: Can't create table (errno: 150) . I suspect this is because I am not adhering to rules laid down in the mysql spec. http://dev.mysql.com/doc/refman/5.4/en/innodb-foreign-key-constraints.html

What does the below portion/rule of the spec mean ?

"InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order."

Does it mean that the foreign keys in table C need to correspond to primary keys in entity and user tables such that the ENTITYID in entity table must be the first in the primary key and NAME in user table must be first in the primary key. In other words should my primary key declarations be rewritten as below ?

entity table -->    primary key(ENTITYID,PID)   , 
user  table -->  primary key(NAME,USERID)

If so , when i attempt to reorder the key declarations as above I run into error 1075. "Incorrect table definition, there can be only one auto column and it must be defined as a key."

How can I make the auto incremented key ( the surrogate key ) to be second in the index order list so that I am in accordance with the spec ?

Thank you !

Answer 1

I don't think your primary keys for entity and user are correct. I believe you have primary key and indexes mixed up.

You have auto increment columns for both tables. I think those should be the primary keys - PID for ENTITY, USERID for USER. If you query for ENTITY using ENTITYID, or USER using NAME, by all means create indexes for both.

Now table C defines a many-to-many relationship between ENTITY and USER. There are two columns in C that point to PID and USERID, with foreign keys for each. The primary key on C is just the combination of the two.

Like this:

create table entity
{
    pid int(20) auto inc not null,
    primary key(pid)
};

create table user
{
    userid int(20) auto inc not null,
    primary key(userid)
};

create table user_entity
{
    entity_id int(20) not null,
    user_id int(20) not null,  
    primary key(entity_id, user_id)  
    foreign key entity_id references entity(pid) on delete cascade,
    foreign key user_id references user(userid) on delete cascade
};

Answer 2

Whenever I did this I definitely, definitely found that it's better to define all the tables first and then apply the constrains with separate ALTER queries later. You avoid so many "I need this table to exist first" problems and the like.