pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuick question re foreign keys.
9+ messages / 6 participants
[nested] [flat]
* Quick question re foreign keys.
@ 2007-10-24 02:03 Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:14 ` Re: Quick question re foreign keys. Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-10-24 02:44 ` Re: Quick question re foreign keys. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Paul Lambert @ 2007-10-24 02:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-10-24 02:14 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
1 sibling, 0 replies; 9+ messages in thread
From: Phillip Smith @ 2007-10-24 02:14 UTC (permalink / raw)
To: 'Paul Lambert' <paul.lambert@autoledgers.com.au>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-10-24 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-24 03:00 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-10-24 02:44 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@autoledgers.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:44 ` Re: Quick question re foreign keys. Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-24 03:00 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 03:09 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 05:27 ` Re: Quick question re foreign keys. D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 2 replies; 9+ messages in thread
From: Paul Lambert @ 2007-10-24 03:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:44 ` Re: Quick question re foreign keys. Tom Lane <tgl@sss.pgh.pa.us>
2007-10-24 03:00 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-10-24 03:09 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 14:27 ` Re: Quick question re foreign keys. Robins Tharakan <tharakan@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Paul Lambert @ 2007-10-24 03:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:44 ` Re: Quick question re foreign keys. Tom Lane <tgl@sss.pgh.pa.us>
2007-10-24 03:00 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 03:09 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-10-24 14:27 ` Robins Tharakan <tharakan@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Robins Tharakan @ 2007-10-24 14:27 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@autoledgers.com.au>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:44 ` Re: Quick question re foreign keys. Tom Lane <tgl@sss.pgh.pa.us>
2007-10-24 03:00 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-10-24 05:27 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-10-24 07:43 ` Re: Quick question re foreign keys. Nis Jørgensen <nis@superlativ.dk>
1 sibling, 1 reply; 9+ messages in thread
From: D'Arcy J.M. Cain @ 2007-10-24 05:27 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@autoledgers.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:44 ` Re: Quick question re foreign keys. Tom Lane <tgl@sss.pgh.pa.us>
2007-10-24 03:00 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 05:27 ` Re: Quick question re foreign keys. D'Arcy J.M. Cain <darcy@druid.net>
@ 2007-10-24 07:43 ` Nis Jørgensen <nis@superlativ.dk>
2007-10-24 14:53 ` Re: Quick question re foreign keys. D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 9+ messages in thread
From: Nis Jørgensen @ 2007-10-24 07:43 UTC (permalink / raw)
To: pgsql-sql; +Cc: Paul Lambert <paul.lambert@autoledgers.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Quick question re foreign keys.
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:44 ` Re: Quick question re foreign keys. Tom Lane <tgl@sss.pgh.pa.us>
2007-10-24 03:00 ` Re: Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 05:27 ` Re: Quick question re foreign keys. D'Arcy J.M. Cain <darcy@druid.net>
2007-10-24 07:43 ` Re: Quick question re foreign keys. Nis Jørgensen <nis@superlativ.dk>
@ 2007-10-24 14:53 ` D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 9+ messages in thread
From: D'Arcy J.M. Cain @ 2007-10-24 14:53 UTC (permalink / raw)
To: Nis Jørgensen <nis@superlativ.dk>; +Cc: pgsql-sql@postgresql.org, Paul Lambert <paul.lambert@autoledgers.com.au>; Tom Lane <tgl@sss.pgh.pa.us>
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-value must 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-10-24 14:53 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-24 02:03 Quick question re foreign keys. Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 02:14 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-10-24 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-24 03:00 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 03:09 ` Paul Lambert <paul.lambert@autoledgers.com.au>
2007-10-24 14:27 ` Robins Tharakan <tharakan@gmail.com>
2007-10-24 05:27 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-10-24 07:43 ` Nis Jørgensen <nis@superlativ.dk>
2007-10-24 14:53 ` D'Arcy J.M. Cain <darcy@druid.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox