Can not create foreign keys in MySql

Question

It want to create 2 tables. 1 table has created but another one is giving error in creating! Note: Both table are Innodb

journal:
---------
jr_date --> primary
entry_no --> primary
description

DDL:

 CREATE TABLE `journal` (
  `jr_date` date NOT NULL,
  `entry_no` smallint(6) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`jr_date`,`entry_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now want to create following table but getting error! Why?

ERROR 1215: Cannot add foreign key constraint   
SQL Statement:

CREATE TABLE `accounting`.`journal_details` (    
  `jr_date` DATE NULL,    
  `entry_no` SMALLINT NULL,    
  `serial` TINYINT NULL,    
  INDEX `fk_journal_details_jr_date_idx` (`jr_date` ASC),    
  INDEX `fk_journal_details_entry_no_idx` (`entry_no` ASC),    
  CONSTRAINT `fk_journal_details_jr_date`
    FOREIGN KEY (`jr_date`)    
    REFERENCES `accounting`.`journal` (`jr_date`)    
    ON DELETE CASCADE    
    ON UPDATE CASCADE,    
  CONSTRAINT `fk_journal_details_entry_no`    
    FOREIGN KEY (`entry_no`)    
    REFERENCES `accounting`.`journal` (`entry_no`)    
    ON DELETE CASCADE
    ON UPDATE CASCADE)

I could not understand why foreign keys are not created?

You will need to post both table DDL, but typically this error is because the types differ in the tables, or because the FK isn't actually a key in the other table. See also http://stackoverflow.com/q/16969060/314291 — StuartLC, Apr 11 '14 at 11:54
check the type of the entry_no column in journal table . It must be smallInt to be used as foreign key to journal_details table.Also to use the **DELETE CASCADE** entry_No should be primary key in Journal table — Shirishkumar Bari, Apr 11 '14 at 11:58
Please get the updated text. Table are innodb, columns datatypes are okay.. but where is the problem? — Iqbal, Apr 11 '14 at 12:01
MySQL likes hiding the actual error message. You have to run `show enginne innodb status` and browse the output. — Álvaro González, Apr 11 '14 at 12:03

StuartLC · Answer 1 · 2014-04-11T12:12:01.717

1

The problem here is that jr_date,entry_no is a composite primary key on table journal. To reference it in journal_details, you will similarly need to reference it as a composite key, not as 2 single simple foreign keys.

i.e. Change the foreign key like so:

  CONSTRAINT `fk_journal_details_jr_date_entry_no`
    FOREIGN KEY (`jr_date`, `entry_no`)    
    REFERENCES `accounting`.`journal` (`jr_date`, `entry_no`)    
    ON DELETE CASCADE    
    ON UPDATE CASCADE,

SqlFiddle here

edited Apr 11 '14 at 12:12

answered Apr 11 '14 at 12:06

StuartLC

104,537
17
209
285

Quite possibly. If so, OP probably also wants to declare `journal_details(jr_date, entry_no)` as primary key. – RandomSeed Apr 11 '14 at 12:12
In general, I would try and avoid composite keys and tend to favour a new, narrow, surrogate. Doing this also avoids common join issues across composite keys. – StuartLC Apr 11 '14 at 12:16

score 1 · Accepted Answer · answered Apr 11 '14 at 12:07

You have a composite primary key:

PRIMARY KEY (`jr_date`,`entry_no`)

It doesn't make any sense to link to those columns separately because they aren't necessarily unique.

I'm pretty sure you only want one foreign key.

  CONSTRAINT `fk_journal_details`
    FOREIGN KEY (`jr_date`, `entry_no`)    
    REFERENCES `accounting`.`journal` (`jr_date`, `entry_no`)    
    ON DELETE CASCADE    
    ON UPDATE CASCADE

RandomSeed · Answer 3 · 2014-04-11T12:17:08.423

0

You lack an index on journals.entry_no.

Reference:

[for each foreign key in the referencing table], there must be [in the referenced table] an index where the referenced columns are listed as the first columns in the same order.

The PRIMARY KEY on journals(jr_date, entry_no) covers for the reference to journal.jr_date, but not for the reference to journals.entry_no;

edited Apr 11 '14 at 12:17

answered Apr 11 '14 at 12:04

RandomSeed

29,301
6
52
87

score 0 · Answer 4 · answered Apr 11 '14 at 12:08

0

Here the primary key is composite key ((jr_date,entry_no))

Primary key is in combination and you try to give reference of a single column which is not a primary key..

answered Apr 11 '14 at 12:08

Aman Aggarwal

17,619
9
53
81

Can not create foreign keys in MySql

4 Answers4