CODEWITHSUNDEEP
mysql
dierre
dierre

Reputation: 7210

Cannot add a foreign key in mysql where a field is a unique index

I'm having problem adding a foreign key based on AUTH_ISLE_CODE:

Create Table: CREATE TABLE `AUTH_ACCOUNT` (
  `ACCOUNT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `PASSWORD` varchar(20) DEFAULT NULL,
  ...
  `AUTH_ISLE_ID` int(11) NOT NULL DEFAULT '1',
  `AUTH_ISLE_CODE` varchar(5) NOT NULL DEFAULT 'AAAAA',
  PRIMARY KEY (`ACCOUNT_ID`),
  KEY `fk_ACCOUNT_ISLE` (`AUTH_ISLE_ID`),
  CONSTRAINT `fk_ACCOUNT_ISLE` FOREIGN KEY (`AUTH_ISLE_ID`) REFERENCES `AUTH_ISLE` (`ISLE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=166 DEFAULT CHARSET=utf8

Create Table: CREATE TABLE `AUTH_ISLE` (
  `ISLE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) DEFAULT NULL,
  `CODE` varchar(5) NOT NULL DEFAULT 'AAAAA',
  PRIMARY KEY (`ISLE_ID`),
  UNIQUE KEY `CODE_UNIQUE` (`CODE`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

the error I'm having is:

ERROR 1005 (HY000): Can't create table 'masterdata.#sql-73c1_57910' (errno: 150)

Is it something about the fact that the two tables have already a relationship with AUTH_ISLE_ID

Edit 1: the SHOW CREATE TABLE you see is the current situation. I'm trying to add just the constraint to AUTH_ISLE_CODE -> CODE from AUTH_ISLE

Upvotes: 0

Views: 43

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

If this is a sequence of queries you are trying to create tables then it will always fail because you are referencing a table REFERENCES AUTH_ISLE (ISLE_ID) which is not created yet ,try creating first AUTH_ISLE table then create your second table AUTH_ACCOUNT,and when AUTH_ISLE is present you can simply reference its columns

 CREATE TABLE `AUTH_ISLE` (
  `ISLE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) DEFAULT NULL,
  `CODE` varchar(5) NOT NULL DEFAULT 'AAAAA',
  PRIMARY KEY (`ISLE_ID`),
  UNIQUE KEY `CODE_UNIQUE` (`CODE`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `AUTH_ACCOUNT` (
  `ACCOUNT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `PASSWORD` varchar(20) DEFAULT NULL,
  `AUTH_ISLE_ID` int(11) NOT NULL DEFAULT '1',
  `AUTH_ISLE_CODE` varchar(5) NOT NULL DEFAULT 'AAAAA',
  PRIMARY KEY (`ACCOUNT_ID`),
  KEY `fk_ACCOUNT_ISLE` (`AUTH_ISLE_ID`),
  CONSTRAINT `fk_ACCOUNT_ISLE` FOREIGN KEY (`AUTH_ISLE_ID`) 
  REFERENCES `AUTH_ISLE` (`ISLE_ID`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=166 DEFAULT CHARSET=utf8;

Demo

insert into AUTH_ISLE (NAME,CODE) values('Test','Test');
insert into AUTH_ACCOUNT (PASSWORD,AUTH_ISLE_ID,AUTH_ISLE_CODE) 
values('Test',last_insert_id(),'Test');

Insert Demo

Upvotes: 1

Related Questions