Re: [GENERAL] Is there any different for foreign key to be serial instead of integer
On Wed, Jan 6, 2010 at 8:36 PM, Scott Marlowe 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there any different for foreign key to be serial instead of integer
On Wed, Jan 6, 2010 at 9:08 PM, Richard Broersma wrote: > On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there any different for foreign key to be serial instead of integer
On Wed, Jan 6, 2010 at 7:51 PM, Yan Cheng Cheok 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there any different for foreign key to be serial instead of integer
On Wed, Jan 6, 2010 at 8:51 PM, Yan Cheng Cheok 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
