Hi everyone. I need to drop a foreign key constraint from a table (or
add an "ON DELETE SET DEFAULT") but the constraint is unnamed. Is
there anyway to do that? I looked online but didn't find anything.

-Brandon

Brandon wrote:
> Hi everyone. I need to drop a foreign key constraint from a table (or
> add an "ON DELETE SET DEFAULT") but the constraint is unnamed. Is
> there anyway to do that? I looked online but didn't find anything.

Use "SHOW CREATE TABLE tablename". It'll show you the constraint name,
which was generated automatically by MySQL when you declared the foreign
key.

Example:

create table prim (i int primary key);
create table fore (i int, foreign key (i) references prim(i));

show create table fore;

CREATE TABLE `fore` (
`i` int(11) default NULL,
KEY `i` (`i`),
CONSTRAINT `fore_ibfk_1` FOREIGN KEY (`i`) REFERENCES `prim` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Regards,
Bill K.

> > Hi everyone. I need to drop a foreign key constraint from a table (or
> > add an "ON DELETE SET DEFAULT") but the constraint is unnamed. Is
> > there anyway to do that? I looked online but didn't find anything.
>
> Use "SHOW CREATE TABLE tablename". It'll show you the constraint name,
> which was generated automatically by MySQL when you declared the foreign
> key.
>
> <SNIP>

Actually, I tried that and it still didn't show an identifier.

Brandon wrote:
>> Use "SHOW CREATE TABLE tablename". It'll show you the constraint name,
>
> Actually, I tried that and it still didn't show an identifier.

This may be dependent on the version of MySQL you use. I tested the
example I gave, using MySQL 5.0.24. What version are you using?

Regards,
Bill K.