2

I would like to ask something that troubles me many many days...

Here is what I mean:

I create these two tables:

CREATE TABLE IF NOT EXISTS journal (
  issn varchar(20) NOT NULL,
  j_title varchar(100) NOT NULL,
  j_publisher varchar(30) NOT NULL,
  PRIMARY KEY (issn)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS volume (
  volume_no int(11) NOT NULL,
  issn varchar(20) NOT NULL,
  year int(11) NOT NULL,
  PRIMARY KEY (issn,volume_no),
  FOREIGN KEY (issn) REFERENCES journal(issn)
) ENGINE=InnoDB;

When I try to create this:

CREATE TABLE IF NOT EXISTS issue (
  issue_no int(11) NOT NULL,
  issue_pages varchar(10) NOT NULL,
  issue_date varchar(10) NOT NULL,
  issn varchar(20) NOT NULL,
  volume_no int(11) NOT NULL,
  PRIMARY KEY (issue_no,issn,volume_no),
  FOREIGN KEY (issn) REFERENCES journal(issn),
  FOREIGN KEY (volume_no) REFERENCES volume(volume_no)
) ENGINE=InnoDB;

it throws an error (errno 150)

The error is in the foreign key volume_no.

Without FOREIGN KEY (volume_no) REFERENCES volume(volume_no)

the table is created without a problem.... I can't explain what's going on... I have seen it many times again and again but nothing!! Does anybody know what's going on?

Thanks in advance!!

Apostolos Kou
  • 85
  • 3
  • 8
  • see http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 – Omesh Aug 14 '12 at 09:13

5 Answers5

3

I could see that the foreign key doesnt include issn but which is actually included in primary key for volumn table.

CREATE TABLE IF NOT EXISTS issue (   issue_no int(11) NOT NULL,  
                                  issue_pages varchar(10) NOT NULL,   
                                  issue_date varchar(10) NOT NULL,  
                                  issn varchar(20) NOT NULL,  
                                  volume_no int(11) NOT NULL,   
                                  PRIMARY KEY (issue_no,issn,volume_no),   
                                  FOREIGN KEY (issn,volume_no) REFERENCES volume(issn,volume_no) ) ENGINE=InnoDB;

Look at the below sql fiddle. http://sqlfiddle.com/#!2/55a63

sundar
  • 1,760
  • 12
  • 28
  • It should work. But the `FOREIGN KEY (issn) REFERENCES journal(issn)` is not needed. – ypercubeᵀᴹ Aug 14 '12 at 09:25
  • Ya.. Agreed. But i used the same table script as that in question. Since volumn is already including `issn` column in foreign key, its duplicate to maintain for journal – sundar Aug 14 '12 at 09:27
0

If the PK of the parent table is more than one field, the order of the fields in the FK must be the same as the order in the PK.

issue: FOREIGN KEY (issn, volume_no) REFERENCES volume(issn, volume_no)

These conditions must be satisfied to not get error 150:

  1. The two tables must be ENGINE=InnoDB.
  2. The two tables must have the same charset.
  3. The PK column(s) in the parent table and the FK column(s) must be the same data type.
  4. The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type;
  5. If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns.

source: MySQL Creating tables with Foreign Keys giving errno: 150

Community
  • 1
  • 1
Omesh
  • 27,801
  • 6
  • 42
  • 51
0

maybe volume_no needs to be UNSIGNED

Jurgo
  • 907
  • 4
  • 18
0

FOREIGN keys must reference a PRIMARY or a UNIQUE key in the parent table.

You need only one Foreign Key at table issue, not two. And it should reference the Primary Key of volume:

CREATE TABLE IF NOT EXISTS issue (
  issue_no int(11) NOT NULL,
  issue_pages varchar(10) NOT NULL,
  issue_date varchar(10) NOT NULL,
  issn varchar(20) NOT NULL,
  volume_no int(11) NOT NULL,
  PRIMARY KEY (issn, volume_no, issue_no),
  FOREIGN KEY (issn, volume_no) 
    REFERENCES volume(issn, volume_no)
) ENGINE=InnoDB;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
0

I had about the same issue with my database. It wasn't about the definition of the foreign key, actually it was the definition of the primary key field.

CREATE TABLE tblProcesses (

fldProcessesID SMALLINT(5) UNIQUE NOT NULL AUTO_INCREMENT ,

CREATE TABLE tblProcessesMessage ( fldProcesses TEXT NOT NULL,

fldProcessesID VARCHAR(15) NOT NULL DEFAULT ,

The primary key in tblProcesses (fldProcessesID) did not have the UNSIGNED keyword while the foreign key in tblProcessesMessage (fldProcessesID) had the UNSIGNED keyword. This keyword was causing the problem - inconsistent type of field. So i added the UNSIGNED keyword to fldProcessesID in tblPreocesses:

CREATE TABLE tblProcesses ( fldProcessesID SMALLINT(5) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,

I hope that this will help you solve your problems. Best regards, Nicholas

Nicholas
  • 247
  • 1
  • 2