CODEWITHSUNDEEP
mysqldatabaserecursionrelation
Mich Vellve
Mich Vellve

Reputation: 75

MySQL Reflexive/recursive relation

I am having a problem trying to create the following table:

CREATE TABLE autor 
(
id_autor VARCHAR (2),
nom_autor VARCHAR (30),
rfc_autor VARCHAR (15),
pseudo_id VARCHAR (2), 
PRIMARY KEY (id_autor),
FOREIGN KEY (pseudo_id) REFERENCES autor(id_autor)
);

When I try to run the following script:

INSERT INTO `revistas`.`autor` (`id_autor`, `nom_autor`, `rfc_autor`,`pseudo_id`) VALUES ('A3', 'Quijote','','A7');

MySQL shows: 

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`revistas`.`autor`, CONSTRAINT `autor_ibfk_1` FOREIGN KEY (`pseudo_id`) REFERENCES `autor` (`id_autor`))

Any idea why?

Thanks a lot!

Upvotes: 0

Views: 4581

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I think you want the reference back to the primary key:

CREATE TABLE autor (
    id_autor VARCHAR (2),
    nom_autor VARCHAR (30),
    rfc_autor VARCHAR (15),
    pseudo_id VARCHAR (2), 
    PRIMARY KEY (id_autor),
    FOREIGN KEY (pseudo_id) REFERENCES autor(id_autor)
---------------------------------------------^
);

This works on SQL Fiddle.

Upvotes: 1

Linger
Linger

Reputation: 15058

I do believe it should be:

FOREIGN KEY (pseudo_id) REFERENCES autor(id_autor)

Upvotes: 2

Related Questions