CODEWITHSUNDEEP
mysqlforeign-keysmysql-error-1005
Guy Fawkes
Guy Fawkes

Reputation: 2441

MySQL FK to non-unique field not works

In MySQL manual I read "However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL.". So, I tried to create two tables:

CREATE TABLE album(
    id INT, 
    title VARCHAR(20)) DEFAULT CHARSET=utf8;
CREATE TABLE track2(   
    album int,   
    dsk INTEGER,   
    posn INTEGER,   
    song VARCHAR(255),   
    FOREIGN KEY (album) REFERENCES album(id) ) default charset=utf8;

and have error: ERROR 1005 (HY000): Can't create table 'b.track2' (errno: 150)

Why can't I to use feature which deviate from SQL standards, but provided by MySQL and described in its manual?

Upvotes: 1

Views: 219

Answers (2)

Guy Fawkes
Guy Fawkes

Reputation: 2441

I understood how to do it:

create table album2 (id int, title varchar(20), key (id));
create table track2 (
     track_id int primary key, 
     album_id int not null, 
     title varchar(100), 
     foreign key (album_id) references album2 (id)
);

mysql> show create table album2;
+--------+-----------------------------
| Table  | Create Table
+--------+-----------------------------
| album2 | CREATE TABLE `album2` (
  `id` int(11) DEFAULT NULL,
  `title` varchar(20) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------

mysql> show create table track2;
+--------+-----------------------------------------------------------------------
| Table  | Create Table
+--------+-----------------------------------------------------------------------
| track2 | CREATE TABLE `track2` (
  `track_id` int(11) NOT NULL,
  `album_id` int(11) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`track_id`),
  KEY `album_id` (`album_id`),
  CONSTRAINT `track2_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `album2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------

All I need is to create non-unique key in album2.

Upvotes: 1

Fahim Parkar
Fahim Parkar

Reputation: 31633

This is happening because you have not set id as PRIMARY KEY in album table.

Use

CREATE TABLE album(
    id INT PRIMARY KEY, 
    title VARCHAR(20)) DEFAULT CHARSET=utf8;

CREATE TABLE track2(   
    album int,   
    dsk INTEGER,   
    posn INTEGER,   
    song VARCHAR(255),   
    FOREIGN KEY (album) REFERENCES album(id) ) default charset=utf8;

Demo

Update 1

Please read

Can a foreign key reference a non-unique index?

Mysql foreign key by non unique key — how is that possible?

Update 2

I believe, your engine is MyISAM. Per MYSQL manual, your statement is valid for InnoDB and NOT MyISAM.

Additionally, MySQL and InnoDB require that the referenced columns be indexed for performance. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys.

Upvotes: 0

Related Questions