Thread: Is there any different for foreign key to be serial instead of integer

I came across a lot of similar example for foreign key

CREATE TABLE orderinfo
(
orderinfo_id serial ,
customer_id integer NOT NULL,
date_placed date NOT NULL,
date_shipped date ,
shipping numeric(7,2) ,
CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id),
CONSTRAINT orderinfo_customer_id_fk FOREIGN KEY(customer_id) REFERENCES
customer(customer_id)
);

instead of let customer_id being type as integer, can i let it be serial? is there any difference?

if the table referenced by customer_id is having primary key typed big serial, customer_id shall be declared as bigint
?

Thanks

On Wed, Jan 6, 2010 at 8:51 PM, Yan Cheng Cheok <yccheok@yahoo.com> wrote:
> I came across a lot of similar example for foreign key
>
> CREATE TABLE orderinfo
> (
> orderinfo_id serial ,
> customer_id integer NOT NULL,
> date_placed date NOT NULL,
> date_shipped date ,
> shipping numeric(7,2) ,
> CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id),
> CONSTRAINT orderinfo_customer_id_fk FOREIGN KEY(customer_id) REFERENCES
> customer(customer_id)
> );
>
> instead of let customer_id being type as integer, can i let it be serial? is there any difference?
>
> if the table referenced by customer_id is having primary key typed big serial, customer_id shall be declared as
bigint? 

serial and big serial are basically syntactic sugar for creating the
table with an int / bigint, create a sequence, create a default for
the bigint field, and setting a dependency in the system catalogs for
the sequence to the table.  So, yep, a serial / bigserial is
equivalent to int / bigint from an FK point of view.

On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok <yccheok@yahoo.com> wrote:


> instead of let customer_id being type as integer, can i let it be serial? is there any difference?
>
> if the table referenced by customer_id is having primary key typed big serial, customer_id shall be declared as
bigint? 

This is a good section about the distinction between:

serial -> integer
bigserial -> bigint

the short answer is that the serial type is actually is the datatype
*integer* with the sequence generator attached to the default value of
the column.

So it only makes sense for primary key to be defined as serial.  A
serial foreign key would be nonsensical since foreign keys should be
be generating their own values.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Wed, Jan 6, 2010 at 9:08 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok <yccheok@yahoo.com> wrote:
>
>
>> instead of let customer_id being type as integer, can i let it be serial? is there any difference?
>>
>> if the table referenced by customer_id is having primary key typed big serial, customer_id shall be declared as
bigint? 
>
> This is a good section about the distinction between:
>
> serial -> integer
> bigserial -> bigint
>
> the short answer is that the serial type is actually is the datatype
> *integer* with the sequence generator attached to the default value of
> the column.
>
> So it only makes sense for primary key to be defined as serial.  A
> serial foreign key would be nonsensical since foreign keys should be
> be generating their own values.

Pretty sure the OP was talking about referencing a bigserial from a
foreign key, which makes perfect sense for certain types of mappings.
FKs don't generate their own values, they reference values in a PK or
unique field somewhere else really.

On Wed, Jan 6, 2010 at 8:36 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>.  A
>> serial foreign key would be nonsensical since foreign keys should be
>> be generating their own values.
>
> Pretty sure the OP was talking about referencing a bigserial from a
> foreign key, which makes perfect sense for certain types of mappings.
> FKs don't generate their own values, they reference values in a PK or
> unique field somewhere else really.

Good catch. :)  I've been sticking my foot in my mouth all day with
typos like this.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug