Database Reference
In-Depth Information
Getting ready
Identify the set of columns that you wish to make unique. Does this apply to all rows, or just a
subset of rows?
Let's start with our example table:
postgres=# SELECT * FROM newcust;
customerid
------------
1
2
3
4
(4 rows)
How to do it...
To prevent duplicate rows, we need to create a unique index that the database server can use
to enforce uniqueness of a particular set of columns.
We can do this in the following three similar ways for basic data types:
1. Create a Primary Key constraint on the set of columns. We are allowed only one
of these per table. The values of the data rows must not be
NULL
, as we force the
columns to be
NOT NULL
if they aren't already.
ALTER TABLE newcust ADD PRIMARY KEY(customerid);
which creates a new index named
newcust_pkey
2. Create a
UNIQUE
constraint on the set of columns. We can use these instead of/as
well as, a
PRIMARY KEY
. There is no limit on the number of these per table. NULLs
are allowed in the columns.
ALTER TABLE newcust ADD UNIQUE(customerid);
which creates a new index named
newcust_customerid_key
3. Create a
UNIQUE INDEX
on the set of columns.
CREATE UNIQUE INDEX ON newcust (customerid);
which creates a new index named
newcust_customerid_idx
All of these techniques exclude duplicates, just with slightly different syntaxes. All of them create
an index, though only the first two create a formal "constraint". Each of those techniques can be
used when we have a primary key or unique constraint that uses multiple columns.
