Question 20073 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql Failed to add the foreign key constraint

I tried to add a foreign key, but it doesn’t work

create database if not exists lukas;
use lukas;
show databases;

create table if not exists buch(
    lel int primary key,
    zeit date
);

create table if not exists preis(
    preis int primary key,
    lel int,
    foreign key(lel) references buch(lel)
);

insert into buch values
    (53, '2006-11-06'),
    (24, '2004-04-23');

insert into preis(preis) values
    (54),
    (43);

Error Code: 3734. Failed to add the foreign key constraint. Missing
column ‘lel’ for constraint ‘preis_ibfk_1’ in the referenced table
‘buch’

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Error Code: 3734. Failed to add the foreign key constraint. Missing column 'lel' for constraint 'preis_ibfk_1' in the referenced table 'buch'

    Wheres the problem in my sql


  2. 0 
    create table if not exists preis(
preis int primary key,
lel int,
FOREIGN KEY (lel) REFERENCES  buch(lel)
);

    You should only mention your field of database and make sure it separate from KEY

    Login or Signup to reply.
  3. 0

    I’ve executed your SQL on my server and it worked well. As I understood, you want tables that don’t allow insertion of null and restrict update, delete on relational fields.

    Check this:

    CREATE TABLE `buch` (
  `lel` int(11) NOT NULL,
  `zeit` date DEFAULT NULL,
  PRIMARY KEY (`lel`)
);

CREATE TABLE `preis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,

  `lel` int(11) NOT NULL,
  `amount` int(11) DEFAULT 0,

  PRIMARY KEY (`id`),

  KEY `preis_lel_to_buch_lel` (`lel`),
  CONSTRAINT `preis_lel_to_buch_lel` 
  FOREIGN KEY (`lel`) REFERENCES `buch` (`lel`) 
  ON DELETE RESTRICT 
  ON UPDATE RESTRICT
);

    You can use CASCADE if you want to update and delete to be cascaded.

    In case of CASCADE, when you delete item from buch, it will delete item in preis too. When you update lel in buch, it will update preis too.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search