Thankyou!

The user interface I was using was automatically adding the same
constraint name and I hadnt noticed :(


Thanks again

Pete

--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
wrote:

> > 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
> >
> >
> >
> >
> >
> >
> >
> >