
August 29th, 2005, 03:50 AM
|
|
Registered User
|
|
Join Date: Jul 2004
Posts: 6
Time spent in forums: 6 m 46 sec
Reputation Power: 0
|
|
|
How to change foreign key from NOT NULL to NULL (allow it with a NULL value)?
Hi all,
I am desperate with the subject.
Let's use the table "CUSTOMER", where is one foreign key defined as:
ADDRESS_ID ADDRESS_DOMAIN NOT NULL
So the "ADDRESS_ID" is the primary key in a table "ADDRESS". Reference to the table ADDRESS was made using SQL "alter table" as usuall. Domain is created that it allows NULL values.
I use EMS Interbase/Firebird manager and cannot change this column to allow NULL values for foreign keys (for example customer without specified address). I can change it but if I try to insert a record then I get following error:
"The insert failed because a column definition includes validation constraints.validation error for column ADDRESS_ID, value "*** null ***"."
But if I create the column as:
ADDRESS_ID ADDRESS_DOMAIN
(i.e. NULL values are allowed) then I am able to insert a record with NULL value for the foreign key.
I cannot drop the column and create a new one as database already contains data.
Can anyone help me how to change it so I can insert record with NULL value for foreign key? I also managed to edit system tables (it seemed to me that EMS manager didn't change all related records) but then I got the error (upon inserting a record):
"Action cancelled by a trigger to preserve data integrity".
Using firebird server 1.5.0.4290 on Linux.
|