0

I am having problems with 2 tables.

I just created a table called "ejemplar" with the following SQL code:

CREATE TABLE ejemplar 
(
id_revista INT NOT NULL,
id_ejemplar INT NOT NULL,
id_art  INT NOT NULL,
fecha_ejem VARCHAR (30),
precio_ejem decimal,
num_pag_ejem INT,
PRIMARY KEY (id_revista,id_ejemplar,id_art),
FOREIGN KEY (id_revista) REFERENCES revista (id_rev)
)

And I want to create a table called "ejem_art" with the following SQL code:

CREATE TABLE ejem_art(
id_rev INT NOT NULL,
id_ejem INT NOT NULL,
id_art INT NOT NULL,
num_pag INT,
PRIMARY KEY (id_rev,id_ejem,id_art),
FOREIGN KEY (id_rev) REFERENCES revista (id_rev),
FOREIGN KEY (id_ejem) REFERENCES ejemplar (id_ejemplar),
FOREIGN KEY (id_art) REFERENCES articulo (id_art)
);

The DBMS is MySQL and as you can see I have the same type of data in the PK and FK fields in both tables.

When I try to run the second SQL code after creating the table "ejemplar" MySQL shows the famous errno: 150 which relates to different type of data or even different engine rather than InnoDB.

I have made sure both things are correct (same data type and same engine) but I still cannot run the second code.

Hope you could help me with this, because I would not want to make an "Alter Table" just because of this error.

Thanks in advance!

Mich Vellve
  • 75
  • 2
  • 8
  • Did you checked the other foreign keys? To be sure that the problem doesn't come from another key. – Joël Salamin Aug 28 '14 at 22:59
  • If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message. I don't see indexes on the referenced keys. – bksi Aug 28 '14 at 23:00
  • Yes. The other FKs are good. The issue is only with these two tables – Mich Vellve Aug 28 '14 at 23:00
  • My previous post is regarding this answer: http://stackoverflow.com/questions/825362/mysql-error-150-foreign-keys – bksi Aug 28 '14 at 23:02
  • I am sorry but what do I have to do bski? AS you can see first I created the table "ejemplar" and then I created the table "ejem_art". Thanks again! – Mich Vellve Aug 28 '14 at 23:03
  • I have tried using ENGINE=InnoDB DEFAULT CHARSET=utf8; in the first table and then crate the 2nd one but I still cannot craete the second table. – Mich Vellve Aug 28 '14 at 23:15

1 Answers1

1

The column you reference in the foreign key must be the leftmost column in a key of the parent table.

CREATE TABLE parent ( A INT, B INT, PRIMARY KEY (A,B) );

CREATE TABLE child (B INT, FOREIGN KEY (B) REFERENCES parent(B) ); /* WRONG */

CREATE TABLE child (A INT, FOREIGN KEY (A) REFERENCES parent(A) ); /* RIGHT */

Re your comments:

It doesn't matter what order the columns are defined in the tables. It matters what order the columns are listed in the PRIMARY KEY constraint. The foreign key can reference only the leftmost column of the parent's key.

In your parent table, you have this primary key:

PRIMARY KEY (id_revista,id_ejemplar),

The column id_revista is leftmost. The column id_ejemplar is not leftmost.

Therefore the child table's foreign key cannot do this:

FOREIGN KEY (id_ejem) REFERENCES ejemplar (id_ejemplar), /* WRONG */

The best solution is to reference both id_revista and id_ejemplar. Then you will be referencing a set of columns of the parent's primary key, starting with the leftmost.

FOREIGN KEY (id_rev, id_ejem) REFERENCES ejemplar (id_revista, id_ejemplar),
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • What do you mean with this? I am sorry, I don't understand what you try to say – Mich Vellve Aug 28 '14 at 23:21
  • I added an example. Can you refresh and see if that is more clear? – Bill Karwin Aug 28 '14 at 23:22
  • I tried the following FOREIGN KEY (id_rev) REFERENCES revista (id_rev), FOREIGN KEY (id_ejem) REFERENCES ejemplar(id_ejem), FOREIGN KEY (id_art) REFERENCES articulo (id_art) and the PK in the parent table are in this order: id_revista INT NOT NULL, id_ejemplar INT NOT NULL, id_art INT NOT NULL, I still cannot make it work – Mich Vellve Aug 28 '14 at 23:32
  • Yes, I see that there are three FK's. You should make the FK reference the *whole* PK from ejemplar. – Bill Karwin Aug 29 '14 at 00:39
  • Isn't each FK supposed to be linked to each of the three tables? Or is this way the right way to link them: FOREIGN KEY (id_rev, id_ejem,id_art) REFERENCES ejemplar (id_revista, id_ejemplar,id_art) – Mich Vellve Aug 29 '14 at 02:14