creating foreign key throws ERROR 1061 (42000): Duplicate key name error
I have this strange error when creating foreign keys on a 2 column "association" table.
step (a) After creating both the parents with the PK I created the child table.
step (b) Then I tried to alter it with 2 FK definitions.
Regardless of what order I try in step (b) I always get the following error for the 2nd constraint
definition:
ERROR 1061 (42000): Duplicate key name 'cm_article_contents'
Please let me know what can be wrong.
SQLS TO EXECUTE:
===================
create parent 1:
===========
create table cm_articles
(
article_id bigint not null,
article_type_id bigint not null,
title varchar(512) null,
web_title varchar(128) null,
deck varchar(256) null,
notes varchar(256) null,
description varchar(512) null,
owner_id bigint null,
start_dt timestamp default current_timestamp null,
end_dt timestamp null,
primary key(article_id)
);
create parent 2:
===========
create table cm_contents
(
content_id bigint not null,
content_type_id bigint null,
content blob null,
content_desc varchar(256) null,
display_seq bigint not null,
versioned char(1) default 'n' not null,
primary key(content_id)
);
create child :
=========
create table cm_article_contents
(
article_id bigint not null,
content_id bigint not null
);
add FK constraint 1:
==============
alter table cm_article_contents
add constraint cm_art_cont_1
foreign key cm_article_contents(article_id) references cm_articles(article_id);
add FK constraint 2:
==============
alter table cm_article_contents
add constraint cm_art_cont_2
foreign key cm_article_contents(content_id) references cm_contents(content_id);