Hello,
I'm currently porting an application using some other DB to MySQL.
The database in question has several tables with foreign key
relations. The application is written such that, on table INSERTs, if
a foreign key relation is violated, the error message is parsed for
the particular relation that is violated. As a simple example, say we
have the tables:
create table mother (mother_id int primary key);
create table father (father_id int primary key);
create table child (child_id int primary key,
mother_id int,
father_id int,
constraint mother_id_fkey
foreign key (mother_id)
references mother (mother_id)
on delete restrict,
constraint father_id_fkey
foreign key (father_id)
references father (father_id)
on delete restrict);
Then if error is encountered on insert into child, the returned error
message is grepped for either 'mother_id_fkey' or 'father_id_fkey',
for example the error message on some other DB might be
ERROR 666: FOREIGN KEY VIOLATED CONSTRAINT (father_id_fkey);
This is much simpler than, say, on error SELECTing tables mother and
father for the missing foreign key. Furthermore, it appears InnoDB at
least, retains the status of the last foreign key error.
Note that the same grepping can be done for any named table
constraint.
QUESTION: is there any way to have error messages in MySQL write the
constraint symbol for the table constraint that was violated, or any
other way to determine the constraint name? It does not appear to be
practical to grep through SHOW INNODB STATUS for one little token...
I apologize if this is in the FAQ, I did make an honest attempt at
trying to find the relevant info in the docs.
Sincerely,
Tetsuji Ueda