pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednull foreign key column
6+ messages / 5 participants
[nested] [flat]
* null foreign key column
@ 2003-02-11 03:55 Arunachalam Jaisankar <jsankar@xtra.co.nz>
2003-02-12 20:41 ` Re: null foreign key column Bruno Wolff III <bruno@wolff.to>
2003-02-12 22:24 ` Re: null foreign key column Dmitry Tkach <dmitry@openratings.com>
0 siblings, 2 replies; 6+ messages in thread
From: Arunachalam Jaisankar @ 2003-02-11 03:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: null foreign key column
2003-02-11 03:55 null foreign key column Arunachalam Jaisankar <jsankar@xtra.co.nz>
@ 2003-02-12 20:41 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2003-02-12 20:41 UTC (permalink / raw)
To: Arunachalam Jaisankar <jsankar@xtra.co.nz>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: null foreign key column
2003-02-11 03:55 null foreign key column Arunachalam Jaisankar <jsankar@xtra.co.nz>
@ 2003-02-12 22:24 ` Dmitry Tkach <dmitry@openratings.com>
2003-02-12 22:35 ` Re: null foreign key column Vernon Wu <vernonw@gatewaytech.com>
1 sibling, 1 reply; 6+ messages in thread
From: Dmitry Tkach @ 2003-02-12 22:24 UTC (permalink / raw)
To: pgsql-sql
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--
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: null foreign key column
2003-02-11 03:55 null foreign key column Arunachalam Jaisankar <jsankar@xtra.co.nz>
2003-02-12 22:24 ` Re: null foreign key column Dmitry Tkach <dmitry@openratings.com>
@ 2003-02-12 22:35 ` Vernon Wu <vernonw@gatewaytech.com>
2003-02-12 23:03 ` Re: null foreign key column Wei Weng <wweng@kencast.com>
2003-02-13 00:19 ` Re: null foreign key column Dmitry Tkach <dmitry@openratings.com>
0 siblings, 2 replies; 6+ messages in thread
From: Vernon Wu @ 2003-02-12 22:35 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, Dmitry Tkach <dmitry@openratings.com>
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)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: null foreign key column
2003-02-11 03:55 null foreign key column Arunachalam Jaisankar <jsankar@xtra.co.nz>
2003-02-12 22:24 ` Re: null foreign key column Dmitry Tkach <dmitry@openratings.com>
2003-02-12 22:35 ` Re: null foreign key column Vernon Wu <vernonw@gatewaytech.com>
@ 2003-02-12 23:03 ` Wei Weng <wweng@kencast.com>
1 sibling, 0 replies; 6+ messages in thread
From: Wei Weng @ 2003-02-12 23:03 UTC (permalink / raw)
To: Vernon Wu <vernonw@gatewaytech.com>; +Cc: pgsql-sql@postgresql.org, Dmitry Tkach <dmitry@openratings.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: null foreign key column
2003-02-11 03:55 null foreign key column Arunachalam Jaisankar <jsankar@xtra.co.nz>
2003-02-12 22:24 ` Re: null foreign key column Dmitry Tkach <dmitry@openratings.com>
2003-02-12 22:35 ` Re: null foreign key column Vernon Wu <vernonw@gatewaytech.com>
@ 2003-02-13 00:19 ` Dmitry Tkach <dmitry@openratings.com>
1 sibling, 0 replies; 6+ messages in thread
From: Dmitry Tkach @ 2003-02-13 00:19 UTC (permalink / raw)
To: vernonw@gatewaytech.com; +Cc: pgsql-sql
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)
>>
>>
>>
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-02-13 00:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-11 03:55 null foreign key column Arunachalam Jaisankar <jsankar@xtra.co.nz>
2003-02-12 20:41 ` Bruno Wolff III <bruno@wolff.to>
2003-02-12 22:24 ` Dmitry Tkach <dmitry@openratings.com>
2003-02-12 22:35 ` Vernon Wu <vernonw@gatewaytech.com>
2003-02-12 23:03 ` Wei Weng <wweng@kencast.com>
2003-02-13 00:19 ` Dmitry Tkach <dmitry@openratings.com>
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