Home > mailing lists

Thread: null foreign key column

null foreign key column

From

"Arunachalam Jaisankar"

Date:

12 February 2003, 18:01:10

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

regards

Jai

Re: null foreign key column

From

Bruno Wolff III

Date:

12 February 2003, 18:27:49

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.

Re: null foreign key column

From

Dmitry Tkach

Date:

12 February 2003, 18:46:12

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> </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>=
>  </DIV>
> <DIV><FONT face=3DArial size=3D2>create table event<BR>(<BR>  &nb=
> sp;=20
> event_id           &=
> nbsp;  =20
> serial           &nb=
> sp;   =20
> not null,<BR>    event_description    &n=
> bsp;=20
> char(255)           =
>          =20
> ,<BR>   =20
> person_id           =
>   =20
> serial           &nb=
> sp;           =20
> ,<BR>    primary key (event_id),<BR>    forei=
> gn=20
> key  (person_id)<BR>       references pe=
> rson=20
> (person_id)<BR>);</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </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--
>

Re: null foreign key column

From

Vernon Wu

Date:

12 February 2003, 20:37:42

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> </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>=
>>  </DIV>
>> <DIV><FONT face=3DArial size=3D2>create table event<BR>(<BR>  &nb=
>> sp;=20
>> event_id           &=
>> nbsp;  =20
>> serial           &nb=
>> sp;   =20
>> not null,<BR>    event_description    &n=
>> bsp;=20
>> char(255)           =
>>          =20
>> ,<BR>   =20
>> person_id           =
>>   =20
>> serial           &nb=
>> sp;           =20
>> ,<BR>    primary key (event_id),<BR>    forei=
>> gn=20
>> key  (person_id)<BR>       references pe=
>> rson=20
>> (person_id)<BR>);</FONT></DIV>
>> <DIV><FONT face=3DArial size=3D2></FONT> </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)
>

Re: null foreign key column

From

Dmitry Tkach

Date:

12 February 2003, 20:40:36

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> </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>=
>>> </DIV>
>>><DIV><FONT face=3DArial size=3D2>create table event<BR>(<BR>  &nb=
>>>sp;=20
>>>event_id           &=
>>>nbsp;  =20
>>>serial           &nb=
>>>sp;   =20
>>>not null,<BR>    event_description    &n=
>>>bsp;=20
>>>char(255)           =
>>>         =20
>>>,<BR>   =20
>>>person_id           =
>>>  =20
>>>serial           &nb=
>>>sp;           =20
>>>,<BR>    primary key (event_id),<BR>    forei=
>>>gn=20
>>>key  (person_id)<BR>       references pe=
>>>rson=20
>>>(person_id)<BR>);</FONT></DIV>
>>><DIV><FONT face=3DArial size=3D2></FONT> </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)
>>
>>    
>>
>
>
>  
>

Re: null foreign key column

From

Wei Weng

Date:

12 February 2003, 21:03:42

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