PostgreSQL ID Field – Referencing Another ID Field

postgresql

I'm on postgresql. I have a table user (id serial not null) and another table client(id) i need to create a :
foreign key client(id) references user(id).

What the client(id) type should be? Int or serial? Thanks for help.

Best Answer

It should be int. The referencing and the refernced columns have to be of the same datatype but it doesn't make sense for a serial to reference another serial because serials are just int with auto incrementing values provided by a sequence.

And when you will be inserting in the second, referencing table (client), you'll want to use the values already inserted in the 1st, referenced table (user). You don't want automatically provided values as these may not match the existing values you already have in the 1st table.

The Postgres docs about numeric datatypes explain:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
Related Question