Member Avatar for terryds

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
  • 266 Views
  • 46 Minutes Discussion Span
  • Latest Post Latest Post by cereal
Member Avatar for cereal

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:

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.