CODEWITHSUNDEEP
mysqlsqldatabaseinnodb
faisal abdulai
faisal abdulai

Reputation: 3819

Foreign Key Constraints In MySQL DB Gives Error

am trying to implement a foreign key constraint but the mysql keeps giving me an error There are two tables "groups" table and "members" table.I have a many to many relationship between these tables and therefore used a third table called "members_groups" table for the many to many relationship. Note: "groups" and "members" tables have been created and contain data but I now want to add the "members_groups" table. Below are the sql codes:

Below is the script for the members table.

CREATE TABLE IF NOT EXISTS `members` (
  `member_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `email` varchar(50) NOT NULL,
  `password` char(128) NOT NULL,
  `salt` char(128) NOT NULL,
  `group_id` bigint(64) unsigned NOT NULL,
  `perm_override_add` bigint(64) unsigned NOT NULL,
  `perm_override_remove` bigint(64) unsigned NOT NULL,
  PRIMARY KEY (`member_id`),
  KEY `member_id` (`member_id`)
) ENGINE=InnoDB;

script for the groups table

CREATE TABLE IF NOT EXISTS `groups` (
  `group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_name` varchar(50) NOT NULL,
  `permission` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB

script for the members_groups table

CREATE TABLE members_groups
(
    member_id INT(11) NOT NULL ,  
    group_id INT(10) NOT NULL ,  
    PRIMARY KEY (member_id, group_id),  
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON UPDATE CASCADE,  
    FOREIGN KEY (group_id) REFERENCES groups(group_id) ON UPDATE CASCADE
)  ENGINE = InnoDB

This is the error I get from the mysql admin console.

enter image description here

Thanks.

Upvotes: 1

Views: 179

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You need to make the type same in your table. In your groups table you have defined

`group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

whereas in your members_groups table it is

group_id INT(10) NOT NULL ,

Try to make the change like

CREATE TABLE members_groups
(
    member_id INT(11) NOT NULL ,  
    group_id INT(10) unsigned NOT NULL ,  --The datatype should be same as group_id in `groups` table
    PRIMARY KEY (member_id, group_id),  
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON UPDATE CASCADE,  
    FOREIGN KEY (group_id) REFERENCES `groups`(group_id) ON UPDATE CASCADE
)  ENGINE = InnoDB;

SQL FIDDLE DEMO

Upvotes: 1

Related Questions