Hi everyone...

I have 2 tables, described here from the metadata (irrelevant columns
replaced with ".."'s):

SET SQL DIALECT 1;

CREATE TABLE HS_GROUPS
(
GROUP_ID INTEGER NOT NULL,
..
ADMIN_USERNAME VARCHAR(100) NOT NULL,
..
CONSTRAINT HS_GROUPS_PK PRIMARY KEY (GROUP_ID)
);

CREATE TABLE HS_USERS
(
USERNAME VARCHAR(100) NOT NULL,
..
CONSTRAINT HS_USERS_PK PRIMARY KEY (USERNAME)
);

I try to run this alter table command:
ALTER TABLE HS_GROUPS ADD CONSTRAINT GROUPS_ADMIN_USER_NAME_FK FOREIGN KEY
(ADMIN_USERNAME) REFERENCES HS_USERS(USERNAME);

As a result, I get the error:
Attempt to store duplicate value (visible to active transactions) in unique
index "RDB$INDEX_16"
Statement: ALTER TABLE HS_GROUPS ADD CONSTRAINT GROUPS_ADMIN_USER_NAME_FK
FOREIGN KEY (ADMIN_USERNAME) REFERENCES HS_USERS(USERNAME)

Reading through the documentation, it seems like this arises when you try to
name a new constraint the same thing as an old constraint, but that is not
what seems to be happening here. I'm wondering if some unique index is
being made by the system in the background in response to the foreign key
creation.

Any help would be greatly appreciated, since I can't make any foreign keys
:)

Thanks,
Mike

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com