Database Reference
In-Depth Information
The preceding query will create a table with a not-null constraint on the
tool_id
column. We can apply the not-null constraint on as many columns as we can.
Consider the following example:
warehouse_db=# CREATE TABLE tools
(
tool_id INTEGER NOT NULL,
tool_name TEXT NOT NULL,
tool_class NUMERIC
);
The preceding query will create the
tools
table with not-null constraints on
tool_id
and
tool_name
.
Exclusion constraints
An
exclusion constraint
is used when comparing two rows on nominative columns
or expressions using the nominative operators. The result of the comparison will be
false or null. Consider the following example in which the conlicting tuple is given
the
AND
operation together:
warehouse_db=# CREATE TABLE movies
(
Title TEXT,
Copies INTEGER
);
Using the
ALTER TABLE
command, we get the following:
warehouse_db=# ALTER TABLE movies
ADD EXCLUDE (title WITH=, copies WITH=);
We will create an exclusion constraint above the
ALTER TABLE
command. The
conditions for a conlicting tuple are
AND
together. Now, in order for two records to
conlict, we'll use the following:
record1.title = record2.title AND record1.copies = record2.copies.
Primary key constraints
In PostgreSQL, we have support for
primary key constraints
, which is actually a
combination of not-null constraints and unique constraints, which means that for a
column to fulill the primary key constraints limitation, it should be unique as well as
not null. Let's create a few tables using primary key constraints:
warehouse_db=# CREATE TABLE tools
(
