> -----Original Message-----
> From: olivier_olmer [mailto:olivier_olmer@...]
> Sent: Thursday, 19 June 2003 9:27 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: Insert succesfull while violating
> primary key constraint
>
>
> 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/
> > >
> > >
>
>
>
> 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/
>
>