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@...]
> 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/
>
>