30

I have the following table:

CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);

When I try to restore it using following command:

psql -U postgres -h localhost -d word -f word.sql

it gives me this error:

multiple primary keys for table "word" are not allowed

How can I use multiple primary keys in postgres?

Timur Shtatland
  • 85
  • 1
  • 10
mostafa
  • 319
  • 1
  • 3
  • 4

2 Answers2

44

how can i use multiple primary keys in postgres ?

You can't. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. You can't have more than one.

You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key for a table.

However, the code you show doesn't produce the error you mentioned:

$ psql -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__
CREATE TABLE
ALTER TABLE
ALTER TABLE
$

At a guess you've actually already defined this table and you're ignoring the previous errors, then showing only the last one. If I re-run this code I get the output:

ERROR:  relation "word" already exists
ALTER TABLE
ERROR:  multiple primary keys for table "word" are not allowed

The real error here is the first one, of course.

I strongly recommend always using -v ON_ERROR_STOP=1 in psql, e.g.:

$ psql -v ON_ERROR_STOP=1 -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__

ERROR:  relation "word" already exists
$

See how it stops at the first error?

(It'd be the default but that'd break backward compatibility).

Craig Ringer
  • 56,343
  • 5
  • 158
  • 190
9

You can define a table like this:

CREATE TABLE mytable (
    field1 INTEGER,
    field2 INTEGER,
    PRIMARY KEY (field1, field2)
);
Demitri
  • 211
  • 2
  • 3