Обсуждение: 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 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.