Dropping an unnamed constraint

[Brandon]

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

[Bill Karwin]

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.

[Brandon]

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.

[Bill Karwin]

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.