> Hi
>
> I have two tables as follows.
> An invoice table:
>
> CREATE TABLE TBLINVOICE (
> INVOICENO INTEGER NOT NULL,
> SCHOOLID INTEGER,
> SOID SMALLINT,
> INVOICEDATE DATE,
> PAID BOOLEAN /* BOOLEAN = CHAR(1) DEFAULT 'n' */,
> DATEPAID DATE,
> THEIRREFERENCE SHORTNOTES /* SHORTNOTES = VARCHAR(255) */,
> NOTES SHORTNOTES /* SHORTNOTES = VARCHAR(255) */
> );
>
> ALTER TABLE TBLINVOICE ADD CONSTRAINT PK_TBLINVOICE PRIMARY KEY
> (INVOICENO);
>
> ALTER TABLE TBLINVOICE ADD CONSTRAINT FK_TBLINVOICE FOREIGN KEY
> (SCHOOLID) REFERENCES TBLCUSTOMER (SCHOOLID) ON DELETE CASCADE ON
> UPDATE CASCADE;
>
> And a table for special offer details:
>
> CREATE TABLE TBLSPECIALOFFERCODE (
> SOID SMALLINT NOT NULL,
> SPECIALOFFERCODE SHORTSTRINGS NOT NULL /* SHORTSTRINGS = CHAR
> (50) */,
> DETAILSFORINVOICE SHORTSTRINGS /* SHORTSTRINGS = CHAR(50) */,
> DISCOUNT FLOAT,
> AMAZONVOUCHERVALUE FLOAT,
> DETAILS SHORTNOTES /* SHORTNOTES = VARCHAR(255) */
> );
>
> ALTER TABLE TBLSPECIALOFFERCODE ADD PRIMARY KEY (SOID);
>
> When I try and add another foreign key to the tblInvoice using:
> alter table TBLINVOICE
> add constraint FK_TBLINVOICE
> foreign key (SOID)
> references TBLSPECIALOFFERCODE(SOID)
> on delete CASCADE
> on update CASCADE
> ALTER TABLE TBLINVOICE ADD CONSTRAINT FK_TBLINVOICE FOREIGN KEY
> (SCHOOLID) REFERENCES TBLCUSTOMER (SCHOOLID) ON DELETE CASCADE ON
> UPDATE CASCADE;

you are trying to add a second constraint with the same name FK_TBLINVOICE -
No?
Alan

>
> I get error:
>
> This operation is not defined for system tables.
> unsuccessful metadata update.
> STORE RDB$INDICES failed.
> attempt to store duplicate value (visible to active transactions) in
> unique index "RDB$INDEX_5".
>
> I have no data in either table, so I'm not sure what is duplicate.
>
> Thanks for the help
>
> Pete
>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>