Hi Alan,

Thanks for the tip of the invisible column RDB$DB_KEY.

If anyone want to test my error with inserting duplicate records
violating the database I can sent a empty database with this problem.
(backup metadata only).

With kind regards,

Olivier Olmer

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

> I've tried this script...
> I don't know the domains you are using (assume integers) but if not

let's

> see them.
> I don't have the other tables you are using either (for FKs) so I

can't see

> that effect if any
> BUT
> I cannot insert that record twice. How did you do it?
> What tool did you use to do it?
>
> As for deleting duplicates
>
> Dimitry Sibiryakov has suggested this one some time ago:
>
> DELETE FROM XXX T1 WHERE EXISTS
> (SELECT * FROM XXX T2 WHERE
> (T2.column1 = T1.column1 or (T2.column1 is null and T2.column1 is
> null)) AND
> (T2.column2 = T1.column2 or (T2.column2 is null and T2.column2 is
> null)) AND
> (.......) AND
> T2.RDB$DB_KEY > T1.RDB$DB_KEY))
>
> Search the group for delete duplicates for other suggestions
>
> Alan
>
> > -----Original Message-----
> > From: olivier_olmer [mailto:olivier_olmer@y...]
> > Sent: Thursday, 19 June 2003 7:01 PM
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Insert succesfull while violating

primary

> > key constraint
> >
> >
> > We experienced succesful inserts in the database while it is
> > violating the primary key constraint.
> > This appeared using firebird 1.02 with multiple files. Even after
> > backup restore this still is the case.
> >
> > This problem does not appear till we dropped some foreign key
> > constraints by its name.
> >
> > Two questions:
> > How do I solve the problem of deleting the double records (since

they

> > are exactly the same?
> > Which version of firebird is reliable on testing the primary key
> > constraint.
> >
> > With kind regards,
> >
> > Olivier Olmer
> >
> > Background info:
> >
> > Here is my metadata retrieved using ibadmin:
> > CREATE TABLE ORDERS (
> > ORDERID ORDERID NOT NULL,
> > ORDERNR ORDERNR ,
> > KLANTID KLANTID ,
> > ORDREF ORDREF ,
> > CREATIONDATE CREATIONDATE ,
> > CHANGEDDATE CHANGEDDATE ,
> > ORDERSTATUSID ORDERSTATUSID DEFAULT 10,
> > CONSTRAINT PKORDERID PRIMARY KEY (ORDERID)
> > );
> >
> > ALTER TABLE ORDERS ADD CONSTRAINT FKORDERSKLANTID FOREIGN KEY
> > (KLANTID) REFERENCES KLANTEN(KLANTID);
> > ALTER TABLE ORDERS ADD CONSTRAINT FKORDERSTATUS FOREIGN KEY
> > (ORDERSTATUSID) REFERENCES ORDERSTATUS(ORDERSTATUSID);
> > ALTER TABLE ORDERS ADD CONSTRAINT CHECKORDERNRNOTNULL CHECK

(ordernr

> > is not null);
> > ALTER TABLE ORDERS ADD CONSTRAINT CHECKORDERNRNOTNUL CHECK
> > (ordernr<>0);
> > CREATE INDEX IDXORDERSORDERNR ON ORDERS(ORDERNR);
> >
> > ***********INSERT INTO orders(
> > ORDERID, ORDERNR, KLANTID, ORDREF, CREATIONDATE, CHANGEDDATE,
> > ORDERSTATUSID)
> > VALUES (
> >
> > 2000, 211930, 117, '---', '2001-08-24 14:50:15', '2001-08-24
> > 15:27:55', 20);
> > INSERT INTO orders(
> > ORDERID, ORDERNR, KLANTID, ORDREF, CREATIONDATE, CHANGEDDATE,
> > ORDERSTATUSID)
> > VALUES (
> >
> > 2000, 211930, 117, '---', '2001-08-24 14:50:15', '2001-08-24
> > 15:27:55', 20);
> >
> > select * from orders where orderid=2000 result in the above

records.

> >
> >
> >
> > To unsubscribe from this group, send an email to:
> > firebird-support-unsubscribe@yahoogroups.com
> >
> >
> >
> > Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/

> >
> >