Thread: Quick question re foreign keys.
I have a table where one column references by foreign key a column from another table. However, the column in this first table does not always contain data which results in a not-null constraint violation when I attempt an insert. My question therefore is, is it possible to create a foreign key that is conditional, i.e. only enforce the foreign key where the value in that table is not null. -- Paul Lambert Database Administrator AutoLedgers
> My question therefore is, is it possible to create a foreign key that is > conditional, i.e. only enforce the foreign key where the value in that > table is not null. My understanding from reading previous threads on this topic is the answer is no, however you could make your own pseudo-foreign key using triggers to do the same job, but only when your column is not null. Not sure of the performance impact of doing this though - someone else may be able to advise pros and cons in more detail. Cheers, ~p THINK BEFORE YOU PRINT - Save paper if you don't really need to print this *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail.
Paul Lambert <paul.lambert@autoledgers.com.au> writes:
> I have a table where one column references by foreign key a column from
> another table. However, the column in this first table does not always
> contain data which results in a not-null constraint violation when I
> attempt an insert.
> My question therefore is, is it possible to create a foreign key that is
> conditional, i.e. only enforce the foreign key where the value in that
> table is not null.
If I'm understanding you correctly, the problem is not the foreign key,
it's that you marked the column NOT NULL. A foreign key constraint by
itself will allow a NULL in the referencing column to pass. You choose
whether you want to allow that or not by separately applying a NOT NULL
constraint or not.
regards, tom lane
Tom Lane wrote: > > If I'm understanding you correctly, the problem is not the foreign key, > it's that you marked the column NOT NULL. A foreign key constraint by > itself will allow a NULL in the referencing column to pass. You choose > whether you want to allow that or not by separately applying a NOT NULL > constraint or not. > > regards, tom lane It's marked not null as a result of being part of the primary key for that table which I can't really get around. I can get away with not having the foreign key though, so I'll have to go down that path. Cheers, P. -- Paul Lambert Database Administrator AutoLedgers
On Wed, 24 Oct 2007 11:00:47 +0800 Paul Lambert <paul.lambert@autoledgers.com.au> wrote: > It's marked not null as a result of being part of the primary key for > that table which I can't really get around. > > I can get away with not having the foreign key though, so I'll have to > go down that path. It can't be the primary key and have NULLs. It sounds to me like you have a design problem somewhere. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
D'Arcy J.M. Cain skrev: > On Wed, 24 Oct 2007 11:00:47 +0800 > Paul Lambert <paul.lambert@autoledgers.com.au> wrote: >> It's marked not null as a result of being part of the primary key for >> that table which I can't really get around. >> >> I can get away with not having the foreign key though, so I'll have to >> go down that path. > > It can't be the primary key and have NULLs. It sounds to me like you > have a design problem somewhere. Well, I have a couple of times had the "need" to have a primary key/uniqueness constraint with one column nullable (indicating "Not Applicable"). The "problem" is that we have only one NULL, which for comparison purposes is interpreted as "Not Known". Nis
Paul Lambert wrote: > > It's marked not null as a result of being part of the primary key for > that table which I can't really get around. > > I can get away with not having the foreign key though, so I'll have to > go down that path. > > Cheers, > P. > Ignore this whole thread actually. I need to rethink some of my design. -- Paul Lambert Database Administrator AutoLedgers
Forgive my butting in, but frankly, most of the times, whenever I find myself in a very 'exceptional problem' such as this one, I always end up questioning the basic design due to which I am stuck in the first place.
Paul, it seems that probably there is a basic design issue here.
All the best :)
Robins
On 10/24/07, Paul Lambert < paul.lambert@autoledgers.com.au> wrote:
Paul Lambert wrote:
>
> It's marked not null as a result of being part of the primary key for
> that table which I can't really get around.
>
> I can get away with not having the foreign key though, so I'll have to
> go down that path.
>
> Cheers,
> P.
>
Ignore this whole thread actually.
I need to rethink some of my design.
--
Paul Lambert
Database Administrator
AutoLedgers
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
On Wed, 24 Oct 2007 09:43:10 +0200 Nis Jørgensen <nis@superlativ.dk> wrote: > Well, I have a couple of times had the "need" to have a primary > key/uniqueness constraint with one column nullable (indicating "Not > Applicable"). The "problem" is that we have only one NULL, which for > comparison purposes is interpreted as "Not Known". Of course. Happens all the time. However, UNIQUE and PRIMARY are not the same thing. PRIMARY implies that the column uniquely and definitively identifies the row. If you have NULLs in the column than it does not meet the criteria. Here are the rules for primary key taken from http://articles.techrepublic.com.com/5100-22-1045050.html. - The primary key must uniquely identify each record.- A record?s primary-key value can?t be null.- The primary key-valuemust exist when the record is created.- The primary key must remain stable?you can?t change the primary-key field(s).- The primary key must be compact and contain the fewest possible attributes. - The primary-key value can?t be changed. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.