Foreign key as primary key doesn't work if FK table has composite primary key (FK is not part of the composite)

Question

two tables, created using these scripts:

create table user_auth
(
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(username, password)
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id),
    FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;

I get the error: Can't create table 'xxx.user' (errno: 150)

A foreign key error.

IF instead I do this (remove foreign key constraint but still reference user_auth):

create table user_auth
(
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(username, password)
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL REFERENCES user_auth(user_id) ON DELETE CASCADE,
    PRIMARY KEY(user_id),
)engine=innodb;

Everything is just peachy, BUT I can insert a user_id into the user table without having a corresponding key in user_auth, which puts a hole in my referential integrity.

Now for kicks say I do this (remove composite key and make user_id a primary key in user_auth):

create table user_auth
(
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(username, password)
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id),
    FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;

This also works, though I do not have the username/password composite to ensure uniqueness.

I have a feeling I'm missing something pretty key in how MySQL works. Please enlighten.

Thanks for your time!

UPDATE

In the article ypercube linked in their answer, point three mentions the order of the PK and corresponding FK must be the same. If I add user_id as a PK in user_auth in the following order, the script works:

create table user_auth
(
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id, username, password)
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id),
    FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;

So I can still SELECT and such, but now I can't make duplicate username/password combos and thus each account will be unique since a username/password/user_id record must exist before I can insert user data.

Answer 1

Point 3 in http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html says everything. So if I add user_id as a PK in user_auth (I thought I mentioned I tried this, but it seems I missed the script example), then it still might not work:

create table user_auth
(
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(username, password, user_id) -- <== DOES NOT WORK
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id),
    FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;

But, if I change the order around...

create table user_auth
(
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id, username, password) -- <== WORKS!
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id),
    FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;

Because in order to user the PK of user_auth as an FK, they must be in the same order as indices, (comment if I am misunderstanding something).

But in terms of table design we still have an issue, the composite key means I could have duplicate user_ids in user_auth, so a smarter design would be:

create table user_auth
(
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id),
    UNIQUE KEY(username) -- <== I should allow duplicate passwords, so just usernames should be               unique
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL,
    PRIMARY KEY(user_id),
    FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;

Much better

The main take-away is that the order of your composite keys matters!

Answer 2

Try:

create table user_auth
(
    username varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    user_auth_id varchar(36) NOT NULL,
    PRIMARY KEY(username, password)
)engine=innodb;

create table user
(
    user_id varchar(36) NOT NULL,
    user_username varchar(36) NOT NULL, 
    user_pass varchar(36) NOT NULL, 
    INDEX(user_username, user_pass),
    PRIMARY KEY(user_id),
    FOREIGN KEY(user_username, user_pass) REFERENCES user_auth(username, password) ON DELETE CASCADE
)engine=innodb;

Reference: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html