| Subject | Broken foreign key constraints in embedded |
|---|---|
| Author | Fredrik Andersson |
| Post date | 2015-06-03T20:25:33Z |
Hi everyone,
We are using Firebird as an embedded database in our Windows application. The Windows application is then installed by customers to run on their own computers. In the past we have seen many different kinds of corruptions in Firebird reported as support cases to us, in most cases we have solved them by performing a backup and restore using the gbak tool. We haven't reported any issues/bugs back here earlier, partly because in the past we haven't been able to keep up to date and use the latest Firebird and ODS version in our software, and partly because we have so little control of the environment that Firebird is running in. But know I got a specific case that I have some questions about, so I thought, why not give it a try.Here are the tables in the database that we have problems with
(
LOG_ID integer NOT NULL,
TIMESTAMPCOLUMN timestamp,
CONSTRAINT INTEG_1 PRIMARY KEY (LOG_ID)
);
CREATE TABLE LOGDATA
(
DATA_ID integer NOT NULL,
DATACOLUMN varchar(255),
LOG_ID integer,
CONSTRAINT INTEG_2 PRIMARY KEY (DATA_ID)
);
ALTER TABLE LOGDATA ADD CONSTRAINT FKCBE0F
FOREIGN KEY (LOG_ID) REFERENCES LOGENTRY (LOG_ID);
In total we have about 100 tables in the database and the tables above also contains some more fields with data. The "corruption" in this case was there 2 entries in the LOGDATA table that pointed to non-existing rows in the LOGENTRY table. And also there were 2 entries with the same primary key in the LOGENTRY table.
The only Firebird version we have been using are 2.5.3 . I have seen that there is a 2.5.4 out now. The only fixed issue that seems relevant for this case I have found is: http://tracker.firebirdsql.org/browse/CORE-4670 but it seems to be when more complex indices are used, ours are just a primary key and a foreign key.
The only Firebird version we have been using are 2.5.3 . I have seen that there is a 2.5.4 out now. The only fixed issue that seems relevant for this case I have found is: http://tracker.firebirdsql.org/browse/CORE-4670 but it seems to be when more complex indices are used, ours are just a primary key and a foreign key.