0

This is my query :

CREATE TABLE userrole (
    userid BIGINT NOT NULL,
    roleid INT NOT NULL,
    PRIMARY KEY(userid,roleid),
    CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES user(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_roleid FOREIGN KEY (roleid) REFERENCES role(role_id) ON DELETE CASCADE ON UPDATE CASCADE
    )

So, what i want to make the userid, and roleid primary key..
And, because userid belongs to the user_id column of user table, and the roleid belongs to the role_id column of the role table,
I add foreign key to them.

But, it generates error.
Please tell me the best way to solve this.

If you say that I shouldn't use foreign key, then :
1. Why shouldn't I use it ?
2. At what occasions should I use it ?
3. What's worth using foreign keys without ON DELETE/UPDATE CASCADE ??

P.S. The table is using InnoDB engine

2
Contributors
1
Reply
11
Views
3 Years
Discussion Span
Last Post by cereal
1

But, it generates error.

Which error?

It could be the definition of the columns, for example userrole.userid here is BIGINT but in users.id is INT, the same applies to the other constraint. Then the constraint label must be unique over the database, so if you have another constraint fk_userid into another table, you have to change it.

For more information check the documentation:

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.