Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesFirebird SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old August 29th, 2005, 03:50 AM
Jiri Jiri is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 6 Jiri User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesFirebird SQL Development > How to change foreign key from NOT NULL to NULL (allow it with a NULL value)?


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway