MySQL FK to non-unique field not works

Question

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?

@GuyFawkes : Mind your language. With this respect no one will care to give an answer... — Fahim Parkar, Jun 20 '12 at 15:30
I only said about me, not something bad about you or somebody else. Thank you for answer, anyway. — Guy Fawkes, Jun 20 '12 at 15:33

score 1 · Answer 1 · answered Jun 21 '12 at 03:32

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.

score 0 · Answer 2 · edited May 23 '17 at 10:34

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.

PK is unique field. And I know that I can to create FK for unique field in other table. But I don't want to set id as PK: in MySQL manual said that I can create FK to non-unique field. — Guy Fawkes, Jun 20 '12 at 15:35

MySQL FK to non-unique field not works

2 Answers2

Demo

Update 1

Update 2