MySQL : how to add foreign key

Question

Hello,

I have the following code in mysql.

create table employee(
    e_id int(10) not null auto_increment,
    user_id int(10),
    usertype_id default 1,
    name varchar(50),
    primary key (e_id)
);
create table relation(
    r_id int(10) not null auto_increment,
    user_id int(10) not null,
    usertype_id int(10) not null,
    interest_id int(10) not null,
    primary key (id)
);

Firstly, i want user_id will have the same value as column e_id;

And then, i want to add user_id and usertype_id as an unity in table relation as a foreign key for user_id and usertype_id in table employee.

Do you know how to do that?

Thanks a lot.

Answer 1

You can make user_id and usertype_id in table relation as foreign keys for the same columns in table employee like this:

create table relation(
    r_id int(10) not null auto_increment,
    foreign key user_id references employee(user_id),
    foreign key usertype_id references employee(usertype_id),
    interest_id int(10) not null,
    primary key (id)
);

I can't help you with e_id and user_id being equal to each other. Honestly, it sounds like a waste of space.

Answer 2

You could introduce a trigger to guarantee that e_id and user_id contain the same value, but I agree with the previous poster. What's the point?