CODEWITHSUNDEEP
sqlpostgresql
jhamm
jhamm

Reputation: 25032

How do I make a serial field auto increment and be a foreign key - Postgres

I have 2 tables and I am trying to create a Foreign Key between the two. Here is the structure of my tables:

create table users (
    id serial, 
    user_name varchar(50)
);

create table playlists (
    id serial, 
    user_id integer references users(id)
);

I keep getting this error:

ERROR:  there is no unique constraint matching given keys for referenced table "users"

Why is there not a unique constraint? If I create the id in the users table as integer PRIMARY KEY, then everything works fine. How do I fix this where the users id auto increments and can be the FK in the playlists table?

Upvotes: 1

Views: 3498

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Creating a column of type serial doesn't make it the primary key or constraint it in any way. serial just creates an integer column, creates a sequence, and attaches the sequence to the column to provide default values. From the fine manual:

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;

If you want your id serial columns to be primary keys (which you almost certainly do), then say so:

create table users (
    id serial not null primary key, 
    user_name varchar(50)
);

create table playlists (
    id serial not null primary key, 
    user_id integer references users(id)
);

Upvotes: 3

Related Questions