From jsankar@xtra.co.nz Tue Feb  11 03:55:46 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 54889475A5C
	for <pgsql-sql@postgresql.org>; Mon, 10 Feb 2003 22:55:46 -0500 (EST)
Received: from mta201-rme.xtra.co.nz (mta201-rme.xtra.co.nz [210.86.15.144])
	by postgresql.org (Postfix) with ESMTP id D1AE8475A4B
	for <pgsql-sql@postgresql.org>; Mon, 10 Feb 2003 22:55:44 -0500 (EST)
Received: from mta1-rme.xtra.co.nz ([210.86.15.143])
	by mta201-rme.xtra.co.nz with ESMTP
	id <20030211035548.LYMU1191.mta201-rme.xtra.co.nz@mta1-rme.xtra.co.nz>
	for <pgsql-sql@postgresql.org>; Tue, 11 Feb 2003 16:55:48 +1300
Received: from singlarc01 ([210.54.102.52]) by mta1-rme.xtra.co.nz with SMTP
	id <20030211035546.ZCVI15912.mta1-rme.xtra.co.nz@singlarc01>
	for <pgsql-sql@postgresql.org>; Tue, 11 Feb 2003 16:55:46 +1300
Message-ID: <000801c2d181$6c0be360$020000c0@singlarc01>
From: "Arunachalam Jaisankar" <jsankar@xtra.co.nz>
To: <pgsql-sql@postgresql.org>
Subject: null foreign key column
Date: Tue, 11 Feb 2003 16:55:28 +1300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0005_01C2D1EE.61998D70"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/254
X-Sequence-Number: 11652

This is a multi-part message in MIME format.

------=_NextPart_000_0005_01C2D1EE.61998D70
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi all,

I would like to have a foreign key column in my table which allows null val=
ue also.
But the below create table sql command doesn't accept null value for person=
_id.
How to do in postgres?

create table event
(
    event_id               serial                not null,
    event_description      char(255)                     ,
    person_id              serial                        ,
    primary key (event_id),
    foreign key  (person_id)
       references person (person_id)
);

regards
Jai
------=_NextPart_000_0005_01C2D1EE.61998D70
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi all,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I would like to have a foreign key column =
in my=20
table which allows null value also.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>But the below create table sql command doe=
sn't=20
accept null value for person_id.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>How to do in postgres?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial size=3D2></FONT>=
&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>create table event<BR>(<BR>&nbsp;&nbsp;&nb=
sp;=20
event_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;=20
serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;=20
not null,<BR>&nbsp;&nbsp;&nbsp; event_description&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;=20
char(255)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
,<BR>&nbsp;&nbsp;&nbsp;=20
person_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;=20
serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
,<BR>&nbsp;&nbsp;&nbsp; primary key (event_id),<BR>&nbsp;&nbsp;&nbsp; forei=
gn=20
key&nbsp; (person_id)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; references pe=
rson=20
(person_id)<BR>);</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>regards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Jai</FONT></DIV></BODY></HTML>

------=_NextPart_000_0005_01C2D1EE.61998D70--




From bruno@wolff.to Wed Feb  12 20:27:48 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 48EC7475BF9
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 15:27:48 -0500 (EST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id 391CC475C14
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 15:27:47 -0500 (EST)
Received: (qmail 30583 invoked by uid 500); 12 Feb 2003 20:41:09 -0000
Date: Wed, 12 Feb 2003 14:41:09 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: Arunachalam Jaisankar <jsankar@xtra.co.nz>
Cc: pgsql-sql@postgresql.org
Subject: Re: null foreign key column
Message-ID: <20030212204109.GB30483@wolff.to>
Mail-Followup-To: Arunachalam Jaisankar <jsankar@xtra.co.nz>,
	pgsql-sql@postgresql.org
References: <000801c2d181$6c0be360$020000c0@singlarc01>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <000801c2d181$6c0be360$020000c0@singlarc01>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/259
X-Sequence-Number: 11657

On Tue, Feb 11, 2003 at 16:55:28 +1300,
  Arunachalam Jaisankar <jsankar@xtra.co.nz> wrote:
> Hi all,
> 
> I would like to have a foreign key column in my table which allows null value also.
> But the below create table sql command doesn't accept null value for person_id.
> How to do in postgres?
> 
> create table event
> (
>     event_id               serial                not null,
>     event_description      char(255)                     ,
>     person_id              serial                        ,
>     primary key (event_id),
>     foreign key  (person_id)
>        references person (person_id)
> );

I don't think you want to use serial type for a value that references
another table. Use integer instead. Serial also implies not null which
is why you couldn't stick null values into that field.



From dmitry@openratings.com Wed Feb  12 20:46:11 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 3D68B475A9E
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 15:46:11 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id B83E1475A97
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 15:46:10 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id A5BEB43A834; Wed, 12 Feb 2003 15:46:10 -0500 (EST)
From: Dmitry Tkach <dmitry@openratings.com>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Re: null foreign key column
Date: Wed, 12 Feb 2003 17:24:49 -0500
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 84
Message-ID: <b2ebqi$9eb$1@news.hub.org>
References: <000801c2d181$6c0be360$020000c0@singlarc01>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
In-Reply-To: <000801c2d181$6c0be360$020000c0@singlarc01>
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/261
X-Sequence-Number: 11659

You don't want it to be serial - just make it 'person_id in'

I hope, it helps...

Dima

Arunachalam Jaisankar wrote:
> This is a multi-part message in MIME format.
> 
> ------=_NextPart_000_0005_01C2D1EE.61998D70
> Content-Type: text/plain;
> 	charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
> 
> Hi all,
> 
> I would like to have a foreign key column in my table which allows null val=
> ue also.
> But the below create table sql command doesn't accept null value for person=
> _id.
> How to do in postgres?
> 
> create table event
> (
>     event_id               serial                not null,
>     event_description      char(255)                     ,
>     person_id              serial                        ,
>     primary key (event_id),
>     foreign key  (person_id)
>        references person (person_id)
> );
> 
> regards
> Jai
> ------=_NextPart_000_0005_01C2D1EE.61998D70
> Content-Type: text/html;
> 	charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
> 
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
> <META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT face=3DArial size=3D2>Hi all,</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> <DIV><FONT face=3DArial size=3D2>I would like to have a foreign key column =
> in my=20
> table which allows null value also.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>But the below create table sql command doe=
> sn't=20
> accept null value for person_id.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>How to do in postgres?</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial size=3D2></FONT>=
> &nbsp;</DIV>
> <DIV><FONT face=3DArial size=3D2>create table event<BR>(<BR>&nbsp;&nbsp;&nb=
> sp;=20
> event_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
> nbsp;&nbsp;&nbsp;=20
> serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
> sp;&nbsp;&nbsp;&nbsp;=20
> not null,<BR>&nbsp;&nbsp;&nbsp; event_description&nbsp;&nbsp;&nbsp;&nbsp;&n=
> bsp;=20
> char(255)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> ,<BR>&nbsp;&nbsp;&nbsp;=20
> person_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
> &nbsp;&nbsp;=20
> serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
> sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
> ,<BR>&nbsp;&nbsp;&nbsp; primary key (event_id),<BR>&nbsp;&nbsp;&nbsp; forei=
> gn=20
> key&nbsp; (person_id)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; references pe=
> rson=20
> (person_id)<BR>);</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
> <DIV><FONT face=3DArial size=3D2>regards</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>Jai</FONT></DIV></BODY></HTML>
> 
> ------=_NextPart_000_0005_01C2D1EE.61998D70--
> 




From vernonw@gatewaytech.com Wed Feb  12 22:37:40 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DBA68474E5C
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 17:37:40 -0500 (EST)
Received: from pd5mo3so.prod.shaw.ca (shawidc-mo1.cg.shawcable.net
	[24.71.223.10]) by postgresql.org (Postfix) with ESMTP id 51717474E44
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 17:37:40 -0500 (EST)
Received: from pd4mr3so.prod.shaw.ca
	(pd4mr3so-qfe3.prod.shaw.ca [10.0.141.214]) by l-daemon
	(iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002))
	with ESMTP id <0HA7009J6W6KR5@l-daemon> for pgsql-sql@postgresql.org;
	Wed, 12 Feb 2003 15:37:32 -0700 (MST)
Received: from pn2ml9so.prod.shaw.ca (pn2ml9so-qfe0.prod.shaw.ca [10.0.121.7])
	by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.6 (built Apr 26
	2002))
	with ESMTP id <0HA700026W6KP3@l-daemon> for pgsql-sql@postgresql.org;
	Wed, 12 Feb 2003 15:37:32 -0700 (MST)
Received: from kimiko (h24-78-132-76.vc.shawcable.net [24.78.132.76])
	by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12
	2002))
	with SMTP id <0HA70076FW6JJ2@l-daemon> for pgsql-sql@postgresql.org;
	Wed, 12 Feb 2003 15:37:32 -0700 (MST)
Date: Wed, 12 Feb 2003 14:35:07 -0800
From: Vernon Wu <vernonw@gatewaytech.com>
Subject: Re: null foreign key column
In-reply-to: <b2ebqi$9eb$1@news.hub.org>
To: pgsql-sql@postgresql.org, Dmitry Tkach <dmitry@openratings.com>
Reply-To: vernonw@gatewaytech.com
Message-id: <FEY83IG42SQA7EB94TR71LGNMVUWSUO.3e4acc1b@kimiko>
MIME-version: 1.0
X-Mailer: Opera 6.05 build 1140
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
X-Priority: 3 (Normal)
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/266
X-Sequence-Number: 11664


12/02/2003 2:24:49 PM, Dmitry Tkach <dmitry@openratings.com> wrote:

>You don't want it to be serial - just make it 'person_id in'

Any reasons?

>
>I hope, it helps...
>
>Dima
>
>Arunachalam Jaisankar wrote:
>> This is a multi-part message in MIME format.
>> 
>> ------=_NextPart_000_0005_01C2D1EE.61998D70
>> Content-Type: text/plain;
>> 	charset="iso-8859-1"
>> Content-Transfer-Encoding: quoted-printable
>> 
>> Hi all,
>> 
>> I would like to have a foreign key column in my table which allows null val=
>> ue also.
>> But the below create table sql command doesn't accept null value for person=
>> _id.
>> How to do in postgres?
>> 
>> create table event
>> (
>>     event_id               serial                not null,
>>     event_description      char(255)                     ,
>>     person_id              serial                        ,
>>     primary key (event_id),
>>     foreign key  (person_id)
>>        references person (person_id)
>> );
>> 
>> regards
>> Jai
>> ------=_NextPart_000_0005_01C2D1EE.61998D70
>> Content-Type: text/html;
>> 	charset="iso-8859-1"
>> Content-Transfer-Encoding: quoted-printable
>> 
>> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
>> <HTML><HEAD>
>> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
>> <META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
>> <STYLE></STYLE>
>> </HEAD>
>> <BODY bgColor=3D#ffffff>
>> <DIV><FONT face=3DArial size=3D2>Hi all,</FONT></DIV>
>> <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
>> <DIV><FONT face=3DArial size=3D2>I would like to have a foreign key column =
>> in my=20
>> table which allows null value also.</FONT></DIV>
>> <DIV><FONT face=3DArial size=3D2>But the below create table sql command doe=
>> sn't=20
>> accept null value for person_id.</FONT></DIV>
>> <DIV><FONT face=3DArial size=3D2>How to do in postgres?</FONT></DIV>
>> <DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial size=3D2></FONT>=
>> &nbsp;</DIV>
>> <DIV><FONT face=3DArial size=3D2>create table event<BR>(<BR>&nbsp;&nbsp;&nb=
>> sp;=20
>> event_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
>> nbsp;&nbsp;&nbsp;=20
>> serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
>> sp;&nbsp;&nbsp;&nbsp;=20
>> not null,<BR>&nbsp;&nbsp;&nbsp; event_description&nbsp;&nbsp;&nbsp;&nbsp;&n=
>> bsp;=20
>> char(255)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
>> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
>> ,<BR>&nbsp;&nbsp;&nbsp;=20
>> person_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
>> &nbsp;&nbsp;=20
>> serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
>> sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
>> ,<BR>&nbsp;&nbsp;&nbsp; primary key (event_id),<BR>&nbsp;&nbsp;&nbsp; forei=
>> gn=20
>> key&nbsp; (person_id)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; references pe=
>> rson=20
>> (person_id)<BR>);</FONT></DIV>
>> <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
>> <DIV><FONT face=3DArial size=3D2>regards</FONT></DIV>
>> <DIV><FONT face=3DArial size=3D2>Jai</FONT></DIV></BODY></HTML>
>> 
>> ------=_NextPart_000_0005_01C2D1EE.61998D70--
>> 
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>






From wweng@kencast.com Wed Feb  12 23:03:40 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 87B56474E5C
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 18:03:40 -0500 (EST)
Received: from mta4.snet.net (mta4.snet.net [204.60.203.76])
	by postgresql.org (Postfix) with ESMTP id EECB3474E44
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 18:03:39 -0500 (EST)
Received: from pop.snet.net (pop.snet.net [204.60.203.72])
	by mta4.snet.net (8.12.3/8.12.3/SNET-mx-1.2/D-1.1.1.1/O-1.1.1.1) with
	ESMTP id h1CN2q2I029861; Wed, 12 Feb 2003 18:02:52 -0500 (EST)
X-Originating-IP: [64.252.95.167]
X-SBCIS-MTA: [pop.snet.net]
Received: from [192.168.1.83] (167.95.252.64.snet.net [64.252.95.167])
	by pop.snet.net (8.12.3/8.12.3/SNET-pop-1.2/D-1.1.1.1/O-1.1.1.1) with
	ESMTP id h1CN3LTV008089; Wed, 12 Feb 2003 18:03:21 -0500 (EST)
Date: Wed, 12 Feb 2003 18:03:56 -0500 (EST)
From: Wei Weng <wweng@kencast.com>
X-X-Sender: wweng@localhost.localdomain
To: Vernon Wu <vernonw@gatewaytech.com>
Cc: pgsql-sql@postgresql.org, Dmitry Tkach <dmitry@openratings.com>
Subject: Re: null foreign key column
In-Reply-To: <FEY83IG42SQA7EB94TR71LGNMVUWSUO.3e4acc1b@kimiko>
Message-ID: <Pine.LNX.4.44.0302121802350.14220-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/268
X-Sequence-Number: 11666

On Wed, 12 Feb 2003, Vernon Wu wrote:

> 
> 12/02/2003 2:24:49 PM, Dmitry Tkach <dmitry@openratings.com> wrote:
> 
> >You don't want it to be serial - just make it 'person_id in'
> 
> Any reasons?

serial is an auto-increment datatype. In your table, you probably want to 
control what person_id is, rather than letting them mindlessly 
auto-increment row after row, given it is a foreign key to a Person table.

> 
> >
> >I hope, it helps...
> >
> >Dima
> >
> >Arunachalam Jaisankar wrote:
> >> This is a multi-part message in MIME format.
> >> 
> >> ------=_NextPart_000_0005_01C2D1EE.61998D70
> >> Content-Type: text/plain;
> >> 	charset="iso-8859-1"
> >> Content-Transfer-Encoding: quoted-printable
> >> 
> >> Hi all,
> >> 
> >> I would like to have a foreign key column in my table which allows null val=
> >> ue also.
> >> But the below create table sql command doesn't accept null value for person=
> >> _id.
> >> How to do in postgres?
> >> 
> >> create table event
> >> (
> >>     event_id               serial                not null,
> >>     event_description      char(255)                     ,
> >>     person_id              serial                        ,
> >>     primary key (event_id),
> >>     foreign key  (person_id)
> >>        references person (person_id)
> >> );
> >> 
> >> regards
> >> Jai




From dmitry@openratings.com Wed Feb  12 22:40:35 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id CBE5E474E5C
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 17:40:35 -0500 (EST)
Received: from exchange.hq.openratings.com (unknown [141.149.165.226])
	by postgresql.org (Postfix) with ESMTP id 2EAD4474E44
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 17:40:35 -0500 (EST)
Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by
	exchange.hq.openratings.com with SMTP (Microsoft Exchange
	Internet Mail Service Version 5.5.2653.13)
	id DQJTWN0P; Wed, 12 Feb 2003 17:40:36 -0500
Message-ID: <3E4AE482.8000908@openratings.com>
Date: Wed, 12 Feb 2003 19:19:14 -0500
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: vernonw@gatewaytech.com
Cc: pgsql-sql@postgresql.org
Subject: Re: null foreign key column
References: <FEY83IG42SQA7EB94TR71LGNMVUWSUO.3e4acc1b@kimiko>
In-Reply-To: <FEY83IG42SQA7EB94TR71LGNMVUWSUO.3e4acc1b@kimiko>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/267
X-Sequence-Number: 11665

Vernon Wu wrote:

>12/02/2003 2:24:49 PM, Dmitry Tkach <dmitry@openratings.com> wrote:
>
>  
>
>>You don't want it to be serial - just make it 'person_id in'
>>    
>>
>
>Any reasons?
>  
>
Yeah... Two of them:

- It does not make sense for a serial column to reference other tables - 
the only purpose of serial is to generate unique keys, in your case you 
do not want them generated, but rather copied from the entries in the 
referenced table.

- The other reason is that 'serial' implies 'not null' - that is why 
your insert statement fails.

Dima

>  
>
>>I hope, it helps...
>>
>>Dima
>>
>>Arunachalam Jaisankar wrote:
>>    
>>
>>>This is a multi-part message in MIME format.
>>>
>>>------=_NextPart_000_0005_01C2D1EE.61998D70
>>>Content-Type: text/plain;
>>>	charset="iso-8859-1"
>>>Content-Transfer-Encoding: quoted-printable
>>>
>>>Hi all,
>>>
>>>I would like to have a foreign key column in my table which allows null val=
>>>ue also.
>>>But the below create table sql command doesn't accept null value for person=
>>>_id.
>>>How to do in postgres?
>>>
>>>create table event
>>>(
>>>    event_id               serial                not null,
>>>    event_description      char(255)                     ,
>>>    person_id              serial                        ,
>>>    primary key (event_id),
>>>    foreign key  (person_id)
>>>       references person (person_id)
>>>);
>>>
>>>regards
>>>Jai
>>>------=_NextPart_000_0005_01C2D1EE.61998D70
>>>Content-Type: text/html;
>>>	charset="iso-8859-1"
>>>Content-Transfer-Encoding: quoted-printable
>>>
>>><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
>>><HTML><HEAD>
>>><META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
>>><META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
>>><STYLE></STYLE>
>>></HEAD>
>>><BODY bgColor=3D#ffffff>
>>><DIV><FONT face=3DArial size=3D2>Hi all,</FONT></DIV>
>>><DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
>>><DIV><FONT face=3DArial size=3D2>I would like to have a foreign key column =
>>>in my=20
>>>table which allows null value also.</FONT></DIV>
>>><DIV><FONT face=3DArial size=3D2>But the below create table sql command doe=
>>>sn't=20
>>>accept null value for person_id.</FONT></DIV>
>>><DIV><FONT face=3DArial size=3D2>How to do in postgres?</FONT></DIV>
>>><DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial size=3D2></FONT>=
>>>&nbsp;</DIV>
>>><DIV><FONT face=3DArial size=3D2>create table event<BR>(<BR>&nbsp;&nbsp;&nb=
>>>sp;=20
>>>event_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
>>>nbsp;&nbsp;&nbsp;=20
>>>serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
>>>sp;&nbsp;&nbsp;&nbsp;=20
>>>not null,<BR>&nbsp;&nbsp;&nbsp; event_description&nbsp;&nbsp;&nbsp;&nbsp;&n=
>>>bsp;=20
>>>char(255)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
>>>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
>>>,<BR>&nbsp;&nbsp;&nbsp;=20
>>>person_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
>>>&nbsp;&nbsp;=20
>>>serial&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
>>>sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
>>>,<BR>&nbsp;&nbsp;&nbsp; primary key (event_id),<BR>&nbsp;&nbsp;&nbsp; forei=
>>>gn=20
>>>key&nbsp; (person_id)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; references pe=
>>>rson=20
>>>(person_id)<BR>);</FONT></DIV>
>>><DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
>>><DIV><FONT face=3DArial size=3D2>regards</FONT></DIV>
>>><DIV><FONT face=3DArial size=3D2>Jai</FONT></DIV></BODY></HTML>
>>>
>>>------=_NextPart_000_0005_01C2D1EE.61998D70--
>>>
>>>      
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>    
>>
>
>
>  
>