1215 SQL code "Cannot add Foreign key Constraint"

Asked

Viewed 410 times

1

When creating this code always comes the error:

1215 "Cannot add Foreign key Constraint"

I’ve tried everything, checked the foreign key restrictions and nothing. Someone can help me? 

create table alunodisc ( 
    codigodisc int, 
    mataluno int , 
    constraint PK primary key(codigodisc,mataluno), 
    constraint codalundisc foreign key (codigodisc) references disciplinas(codisc), 
    constraint matalundisc foreign key (mataluno) references alunos(mataluno)
);
mysql sql foreign-key
  • 1

    Which database engine? SQL Server? Mysql? Sqlite?

  • I use the Mysql.

  • https://www.scriptbrasil.com.br/forum/topic/176772-resolvido-error-1215-cannot-add-foreign-key-constraint/

  • 1

    Place the table structure alunos and disciplinas.

  • The error is stating that you cannot add a Foreign Key Constraint, I believe you are referencing a wrong column name. 1 - Note how the column name of each table is written. 2 - Make sure you put primary keys in the columns you are referencing. 3 - Check that the column names are equal in the reference. Anyway, everything indicates that it is typing error, the bad of mysql and other banks that it can’t show exactly where the error is, you have to check line by line and sometimes the person passes by.

2 answers

0

To find the specific error, run this:

SHOW ENGINE INNODB STATUS;

And look at the part about LATEST FOREIGN KEY ERROR.

The column that will be foreign and the key that it references must be equal in types. For example one cannot be SMALLINT and the other INT

Also, you should run the query set foreign_key_checks = 0 before running the DDL so that you can create the tables in an arbitrary order (Raising daughters before parents), if you do not do this you should create the parent tables first, after those that use as key some attribute of the father.

0

Pointing out things that draw attention to your code:

  1. The foreign reference in FK codalundisc could be a typo: codisc or codigodisc?
  2. Foreign fields must be PK of their respective tables, as well as of the same type of data in both tables (int, in the present case).
  3. (foreign) referenced tables must be created before Fks that reference them.

whereas there is no typo as suggested in item 1, check the Pks and the order of table creation. The code below works (SQL Fiddle):

create table disciplinas (codisc int primary key);
create table alunos(mataluno int primary key);

create table alunodisc ( 
    codigodisc int, 
    mataluno int , 
    constraint PK primary key(codigodisc,mataluno), 
    constraint codalundisc foreign key (codigodisc) references disciplinas(codisc), 
    constraint matalundisc foreign key (mataluno) references alunos(mataluno)
);

  • Just one remark: Mysql does not require the field referenced in the foreign key to be PK, only that the field is indexed. Note that Mysql does not even require it to be declared as UNIQUE and NOT NULL, as other DBMS require.

Browser other questions tagged mysql sql foreign-key

You are not signed in. Login or sign up in order to post.