--- In firebird-support@yahoogroups.com, "Maurizio P." <mauriz_po@...>
wrote:

>
> hi ,
> i am tring to add a foreign key to a table which contains some data ,
> and it seems i can't if i don't delete all the data to the (child)

table ,

> fortunately it is only test data : i delete data and i can add the FK
> without any trouble .
> ... is it possible to add a FK without deleting data ?
>
> .... or maybe i am wrong in other ways , here the error message :
> violation of FOREIGN KEY constraint "".
> violation of FOREIGN KEY constraint "PK_TCAUDOCUMENTI" on table
> "TCAUDOCUMENTI".
>
> i use Firebird 1.5. and ibExpert .

That is incorrect.

Foreign keys may be added retrospectively providing for every record
in the child table there exists a record in the master table. Your
error is unusual though, why would you call a foreign key constraint
PK_TCAUDOCUMENTI? That seems to me to be a primary key naming
convention. Your unnamed foreign key constraint is also not helping you.

Possibly you have '' values inside the field instead of null (null is
not the same as an empty string or 0).

---

CREATE TABLE A (AID INTEGER NOT NULL, PRIMARY KEY (AID));
CREATE TABLE B (BID INTEGER NOT NULL, AID INTEGER, PRIMARY KEY (BID));
COMMIT;

INSERT INTO B VALUES (1, 1);
INSERT INTO B VALUES (2, 1);
INSERT INTO B VALUES (3, 1);
COMMIT;

-- You can not define the foreign key now because there is no
corresponding record in A.

INSERT INTO A VALUES (1);
COMMIT;

-- Now we can.

ALTER TABLE B ADD CONSTRAINT FK_B_AID FOREIGN KEY (AID) REFERENCES A
(AID) ON UPDATE CASCADE ON DELETE CASCADE;
COMMIT;

---

Adam