PostgreSQL: Primary Keys

Question

According to PostgreSQL documentation a table can have at most one primary key (while it can have many unique and not-null constraints).

CREATE TABLE products (
    product_no integer PRIMARY KEY,
   name text,
   price numeric
);

So what happens when you make two columns the primary keys like in the table below?

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

Is the "main" primary key "a" or is just a unique/not null constraint set for a and c? How does postgresql know which one is the main primary key?

Any help would be great. Thanks

Also what would the following produce? Would the primary key still be the commination of a and c or would a be the primary key and c would just be a constraint that is unique/not null?

CREATE TABLE example (
    a integer PRIMARY KEY,
    b integer,
    c integer,
    PRIMARY KEY (c)
);

After testing the above produces:

ERROR:  multiple primary keys for table "example" are not allowed
LINE 5:     PRIMARY KEY (c)

And yet a third case which may be the same as the one above:

CREATE TABLE example (
    a integer PRIMARY KEY,
    b integer,
    c integer PRIMARY KEY
);

After testing the above produces:

ERROR:  multiple primary keys for table "example" are not allowed
LINE 4:     c integer PRIMARY KEY

Audrius Meškauskas · Accepted Answer · 2014-05-21 19:04:09Z

3

In the second case, the combination of a and c is the one and only primary key of DB table example. That means that a may not be unique, c may not be unique but the combination of a and c should be unique per row. In that sense there is not such thing as "a is the main primary key".

Your third and fourth case are not valid PostgreSQL code. I used SQL Fiddle to confirm it.

edited May 21, 2014 at 19:04

Audrius Meškauskas

21.9k12 gold badges85 silver badges100 bronze badges

answered May 21, 2014 at 18:17

Alexandros

2,2004 gold badges27 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wwjdm Over a year ago

ok, this is great. I used the word "main" because a table can have only 1 primary key. In this case the "main" primary key is the combination of a and c.

wwjdm Over a year ago

Also, what would happen in the second case. See edited question.

Alexandros Over a year ago

@EliMiller I am not sure this is valid PostgreSQL code. Have you tested it?

Alexandros Over a year ago

@EliMiller I do not know if you know it (you probably do) but sqlfiddle.com is excellent for testing valid code for DB flavors. So, you can check it in person, if you want.

Collectives™ on Stack Overflow

PostgreSQL: Primary Keys

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related