PostgreSQL/Constraints/Primary Key

Add PRIMARY KEY in table creation

postgres=#
postgres=# CREATE TABLE new_books
postgres-#               (id integer CONSTRAINT books_id_pkey PRIMARY KEY,
postgres(#               title text NOT NULL,
postgres(#               author_id integer,
postgres(#               subject_id integer);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "new_books"
CREATE TABLE
postgres=#
postgres=# drop table new_books;
DROP TABLE
postgres=#

Implicit index is created when creating a table for a primary key

postgres=#
postgres=# CREATE TABLE authors (id integer PRIMARY KEY,
postgres(#                       last_name text,
postgres(#                       first_name text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
CREATE TABLE
postgres=#
postgres=# drop table authors;
DROP TABLE
postgres=#

Indicate one column as the primary key column

postgres=#
postgres=# CREATE TABLE "authors" (
postgres(#      "id" integer NOT NULL,
postgres(#      "last_name" text,
postgres(#      "first_name" text,
postgres(#      Constraint "authors_pkey" Primary Key ("id")
postgres(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
CREATE TABLE
postgres=#
postgres=#
postgres=# CREATE TABLE distinguished_authors (award text)
postgres-#               INHERITS (authors);
CREATE TABLE
postgres=#
postgres=# \d distinguished_authors
Table "public.distinguished_authors"
   Column   |  Type   | Modifiers
------------+---------+-----------
 id         | integer | not null
 last_name  | text    |
 first_name | text    |
 award      | text    |
Inherits: authors
postgres=#
postgres=# drop table distinguished_authors;
DROP TABLE
postgres=# drop table authors;
DROP TABLE
postgres=#

Primary keys constrains more than one column

postgres=#
postgres=# -- Primary keys constrains more than one column
postgres=#
postgres=# CREATE TABLE example (
postgres(#    a integer,
postgres(#    b integer,
postgres(#    c integer,
postgres(#    PRIMARY KEY (a, c)
postgres(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "example_pkey" for table "example"
CREATE TABLE
postgres=#
postgres=# drop table example;
DROP TABLE
postgres=#
postgres=#

PRIMARY KEY will create implicit index

postgres=#
postgres=#
postgres=# CREATE TABLE products (
postgres(#    product_no integer PRIMARY KEY,
postgres(#    name text,
postgres(#    price numeric
postgres(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "products_pkey" for table "products"
CREATE TABLE
postgres=#
postgres=# drop table products;
DROP TABLE
postgres=#
postgres=#

Primary key with check option

postgres=#
postgres=# CREATE TABLE employee
postgres-#              (id integer PRIMARY KEY CHECK (id > 100),
postgres(#               last_name text NOT NULL,
postgres(#               first_name text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
postgres=#
postgres=# insert into employee values(1000, "a", "b");
INSERT 0 1
postgres=# insert into employee values(200,  "c",  "d");
INSERT 0 1
postgres=# insert into employee values(11,   "a",   "b");
ERROR:  new row for relation "employee" violates check constraint "employee_id_check"
postgres=#
postgres=# select * from employee;
  id  | last_name | first_name
------+-----------+------------
 1000 | a         | b
  200 | c         | d
(2 rows)
postgres=#
postgres=# drop table employee;
DROP TABLE
postgres=#

Use sequence value as a primary key

postgres=#
postgres=# CREATE SEQUENCE myseq MINVALUE 0;
postgres=# -- Create auto-incrementing DEFAULT and PRIMARY KEY constraint, is:
postgres=#
postgres=# CREATE TABLE shipments
postgres-#          (id integer DEFAULT nextval(""myseq""::text) PRIMARY KEY,
postgres(#           customer_id integer,
postgres(#           isbn text,
postgres(#           ship_date timestamp);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "shipments_pkey" for table "shipments"
CREATE TABLE
postgres=#
postgres=# drop sequence myseq;
DROP SEQUENCE
postgres=# drop table shipments;
DROP TABLE
postgres=#
postgres=#