1

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.

1

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;
  • 1
    Perfect answer it's the thing i was thinking about but I should better check the documentation next time. Thanks – Federico Ponzi Aug 27 '14 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.