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)
>>
>>    
>>
>
>
>  
>