[Resolved] Postgresql Serial type and compatibility

[jlpierce]

Hello all,

if I create a schema with a master table and various child tables and use the SERIAL type for the primay key of the master table and want to insure referential integrity in the child tables what data type is compatible with the SERIAL type.

It makes sense to me that I want a type other than SERIAL in the child tables as SERIAL would create a unique number for the children as well, correct?

I quess my first question should have been is SERIAL the equivelant of an auto increment field?

TIA

John

[kparker]

SERIAL is just shorthand for creating a sequence, and then using that sequence as the default value for an INT4 column. If you use the command-line tool psql, you'll even see that it tells you that's what will happen.

So: all SERIAL does for you is get an automatic unique number assigned to the column when inserting a new row (provided you don't supply one of your own with the INSERT statement.) Other tables that want to reference this number should use INT4 as the data type, and you still have to explicitly say PRIMARY KEY if you want it to be such.

[jlpierce]

Thanks Kirk!

I was pretty sure that I understood serial to be an auto increment, Int4 is the datatype then and I can create child tables with int4 as the pk and reference the master pk with a foreign key.

Thanks alot for your explanation.

John