Problem with FK in MySQL

Question

Problem with FK in MySQL

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

I am somewhat new to the database design and I have the following doubt: I have created a bd called "newdb" in which I create two tables (student and subject) with the following columns:

Student table

Field               Type    Null    Key     Default Extra
codigo_alumno       int(11) NO      PRI     NULL    auto_increment
nombre              text    NO      NULL    
materia_inscripto   int(11) NO      UNI     NULL

Table subject

Field               Type    Null    Key     Default Extra
codigo_asignatura   int(11) NO      PRI     NULL    auto_increment
nombre_asignatura   text    NO      NULL    
numero              int(11) NO      MUL     NULL

I would like to be able to add an FK that would allow me to relate the column 'asignatura_inscripto' of the 'student' table with the column 'number' of the subject table. The fact is that working with MySQL (InnoDB engine) does not allow it:

ALTER TABLE alumno ADD FOREIGN KEY ( materia_inscripto ) REFERENCES    asignatura ( numero ) ON DELETE SET NULL ON UPDATE CASCADE; MySQL has   said: Documentation

1215 - Unknown error 1215

Even rarer (at least, with my ignorance of the subject) is that if you allow me if I do not modify the "ON UPDATE" and "ON DELETE":

Your query was successfully executed. ALTER TABLE alumno ADD FOREIGN KEY ( materia_inscripto ) REFERENCES asignatura ( numero ) ON DELETE RESTRICT ON UPDATE RESTRICT;

I already appreciate all the help you can give me. I have consulted several pages within the site and I do not think any frames with the same doubt that I have (several were solved by applying the same type of collation to the tables, others with the same type of data [in my case, everything is the same: all to utf8 and the related fields are unsigned integers]).

Note

If I forgot to specify any more data that is considered relevant, I am careful to be able to explain it later :). Greetings.

mysql sql

asked by Faju 14.09.2017 в 19:27

source

2 answers

1

I think it's because your column student_code allows null values and as I understand that it should not be. That is, DEFAULT can not be null a primary field

answered by 14.09.2017 в 20:20

How to distribute a .jar on mac using .dmg with Drag & Drop? How do I use serialized data in Python?

score 2 · Accepted Answer

I recreated your database and I have achieved this:

I create a index in table asignatura for field number:

ALTER TABLE 'prueba'.'asignatura' 
ADD INDEX 'indexNumero' ('numero' ASC);

I create a index in table alumno for field materia_inscripto :

ALTER TABLE 'prueba'.'alumno' 
ADD UNIQUE INDEX 'indexinscripto' ('materia_inscripto' ASC);

I create the key:

ALTER TABLE 'prueba'.'alumno' 
ADD CONSTRAINT 'FKinscripto'
    FOREIGN KEY ('materia_inscripto')
    REFERENCES 'prueba'.'asignatura' ('numero')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

I hope it will help you, and I would recommend you use MySQL Workbench, to make your tasks a bit easier.