Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Using Constraints

Constraints are table attributes used to define rules on the type of data values allowed within specified columns. By enforcing these rules within the database, you can effectively safeguard against incorrect or inappropriate data being added to the database.

When you create a table, you can create a constraint using the CREATE TABLE command's CONSTRAINT clause. There are two types of constraints: column constraints and table constraints.

Column constraints apply only to a single column, while table constraints may apply to one or more columns. Within the CREATE TABLE command, the syntax for a column constraint follows immediately after a column definition, whereas the syntax for a table constraint exists in its own block, separated by a comma from any existing column definitions. A table constraint relies on its definition, rather than placement in the syntax, to indicate the columns affected by its restrictions.

The following sections discuss the different rules a constraint can enforce.

Column constraints

Performing the \h CREATE TABLE slash command within psql displays several detailed syntax diagrams for the constraints that may be placed on a table. Here is the syntax for a column constraint:

  [ CONSTRAINT 
constraint_name
 ]
  { NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT 
value
 | CHECK ( 
condition
 ) |
    REFERENCES table [ ( 
column
 ) ]
               [ MATCH FULL | MATCH PARTIAL ]
               [ ON DELETE 
action
 ]
               [ ON UPDATE 
action
 ]
               [ DEFERRABLE | NOT DEFERRABLE ]
               [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  }

This syntax immediately follows the data type of the column to be constrained (and precedes any commas separating it from other columns) in the CREATE TABLE statement. It may be used with as many columns as is necessary. Notice that the CONSTRAINT keyword and constraint_name identifier are optional, and may be omitted.

There are six sets of column constraint keywords that may be applied. Some of the effects of these constraints are implicitly defined by others. The constraint keywords are as follows:

NOT NULL

Specifies that the column is not allowed to contain NULL values. Using the constraint CHECK ( column NOT NULL) is equivalent to using the NOT NULL constraint.

UNIQUE

Specifies that the same value may not be inserted in this column twice. Note that the UNIQUE constraint allows more than one NULL value to be in a column, as NULL values technically never match another value.

PRIMARY KEY

Implies both UNIQUE and NOT NULL constraints, and causes an index to be created on the column. A table is restricted to having only one primary key constraint.

DEFAULT value

Causes unspecified input values to be replaced with a default value of value . This value must be of the same data type as the column it applies to. PostgreSQL 7.1.x does not support subselects as default values.

CHECK condition

Defines a condition that the value must satisfy for an INSERT or UPDATE operation to succeed on that row. The condition is an expression that returns a Boolean result. Used as a column constraint, only the one column being defined can be referenced by the CHECK clause.

The sixth column constraint, REFERENCES, contains the following clauses:

REFERENCES table [ ( column ) ]

Input values to the constrained column are checked against the values of the column column within the table table. If a matching value on this column is not found in the column that it references, the INSERT or UPDATE will fail. If column is omitted, the primary key on table is used, if one exists.

This column constraint is similar to the FOREIGN KEY table discussed in the next section. Functionally, the REFERENCES column constraint is very similar to a FOREIGN KEY column constraint.

See Example 7-8 for an example of a table being created with a FOREIGN KEY table constraint.

MATCH FULL | MATCH PARTIAL

The MATCH clause affects what kind of NULL and non-NULL values are allowed to be mixed on insertion into a table whose foreign key references multiple columns . The MATCH clause is therefore only practically applicable to table constraints, though the syntax is technically valid in a column constraint as well.

MATCH FULL disallows insertion of row data whose columns contain NULL values unless all referenced columns are NULL. As of PostgreSQL 7.1.x, MATCH PARTIAL is not supported. Not specifying either clause allows NULL columns to satisfy the constraint.

Again, as column constraints may only be placed on a single column, this clause is only directly applicable to table constraints.

ON DELETE action

When a DELETE is executed on a referenced row in the referenced table, one of the following actions will be executed upon the constrained column, as specified by action :

NO ACTION

Produces an error if the reference is violated. This is the default if action is not specified.

RESTRICT

Identical to NO ACTION.

CASCADE

Removes all rows which reference the deleted row.

SET NULL

Assigns a NULL value to all referenced column values.

SET DEFAULT

Sets all referenced columns to their default values.

ON UPDATE action

When an UPDATE statement is performed on a referenced row in the referenced table, the same actions are available as with the ON DELETE clause. The default action is also NO ACTION.

Specifying CASCADE as the ON UPDATE action updates all of the rows that reference the updated row with the new value (rather than deleting them, as would be the case with ON DELETE CASCADE).

DEFERRABLE | NOT DEFERRABLE

DEFERRABLE gives you the option of postponing enforcement of the constraint to the end of a transaction rather than having it enforced at the end of each statement. Use the INITIALLY clause to specify the initial point at which the constraint will be enforced.

NOT DEFERRABLE means the enforcement of the constraint must always be done immediately as each statement is executed. Users do not have the option to defer enforcement to the end of a transaction when this is set. This is the default.

INITIALLY DEFERRED | INITIALLY IMMEDIATE

The constraint must be DEFERRABLE in order to specify the INITIALLY clause. INITIALLY DEFERRED causes enforcement to be postponed until the end of the transaction is reached, whereas INITIALLY IMMEDIATE causes constraint checking to be performed after each statement. INITIALLY IMMEDIATE is the default when the INITIALLY clause is not specified.

Example 7-7 shows how to create a table called employees with a variety of simple constraints.

Example 7-7. Creating a table with column constraints

booktown=# 
CREATE TABLE employees

booktown-# 
            (id integer PRIMARY KEY CHECK (id > 100),

booktown(# 
             last_name text NOT NULL,

booktown(# 
             first_name text);

NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'employees_pkey'
for table 'employees'
CREATE

Example 7-7 creates a column called id, of type integer which has both a PRIMARY KEY constraint and a CHECK constraint. The PRIMARY KEY constraint implies both NOT NULL and UNIQUE, as well as implicitly creates the employees_ pkey index to be used on the column. The CHECK constraint verifies that the value of id is greater than 100. This means that any attempt to INSERT or UPDATE row data for the employees table with an id value of less-than or equal-to 100 will fail.

The employees table created in Example 7-7 also contains a column named last_name of type text which has a NOT NULL constraint enforced. This is a much simpler constraint; it disallows the addition of employees whose last name values are input as NULL. In other words, users must supply a last name for each employee.

Note: Conditions set with the CHECK clause must involve values of comparable data types.

Table constraints

Unlike column constraints, a table constraint can be defined on more than one column of a table. Here is the syntax to create a table constraint:

  [ CONSTRAINT 
constraint_name
 ]
  { UNIQUE ( 
column
 [, ...] ) |
    PRIMARY KEY ( 
column
 [, ...] ) |
    CHECK ( 
condition
 ) |
    FOREIGN KEY ( 
column
 [, ... ] )
                REFERENCES table [ ( 
column
 [, ... ] ) ]
                [ MATCH FULL | MATCH PARTIAL ]
                [ ON DELETE 
action
 ]
                [ ON UPDATE 
action
 ]
                [ DEFERRABLE | NOT DEFERRABLE ]
                [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CONSTRAINT constraint_name provides an optional name for the constraint. Naming a constraint is recommended, as it provides you with a meaningful name for the purpose of the constraint, rather than an automatically generated, generic name. In the future, this name also may be useful in removing constraints (e.g., when PostgreSQL's DROP CONSTRAINT clause of the ALTER TABLE implemented). The other clauses define four general types of table constraints:

PRIMARY KEY ( column [, ...] )

The PRIMARY KEY table constraint is similar to the PRIMARY KEY column constraint. As a table constraint, PRIMARY KEY allows multiple columns to be defined in a parenthetical expression, separated by commas. An implicit index will be created across columns. The combination of values for each column specified must therefore amount to only unique and non-NULL values, as with the PRIMARY KEY column constraint.

UNIQUE ( column [, ...] )

Specifies that the combination of values for the columns listed in the expression following the UNIQUE keyword may not amount to duplicate values. NULL values are allowed more than once, as NULL technically never matches any other value.

CHECK ( condition )

Defines a condition that incoming row data must satisfy for an INSERT or UPDATE operation to succeed. The condition is an expression that returns a Boolean result. Used as a table constraint, more than one column can be referenced by the CHECK clause.

FOREIGN KEY ( column [, ... ] ) REFERENCES table [ ( column [, ... ] ) ]

Allows multiple columns to be specified as the source for the REFERENCES clause. The syntax following the FOREIGN KEY clause and its specified columns is identical to that of the column REFERENCES constraint.

Example 7-8 creates the Book Town editions table. It creates three table constraints. A detailed explanation follows the example.

Example 7-8. Creating a table with table constraints

booktown=# 
CREATE TABLE editions

booktown-# 
             (isbn text,

booktown(# 
             book_id integer,

booktown(# 
             edition integer,

booktown(# 
             publisher_id integer,

booktown(# 
             publication date,

booktown(# 
             type char,

booktown(# 
             CONSTRAINT pkey PRIMARY KEY (isbn),

booktown(# 
             CONSTRAINT integrity CHECK (book_id IS NOT NULL

booktown(# 
                                         AND edition IS NOT NULL),

booktown(# 
             CONSTRAINT book_exists FOREIGN KEY (book_id)

booktown(# 
                        REFERENCES books (id)

booktown(# 
                        ON DELETE CASCADE

booktown(# 
                        ON UPDATE CASCADE);

NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'pkey' for table
'editions'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

The first constraint, pkey is a PRIMARY KEY constraint on the isbn column, and behaves identically to a PRIMARY KEY column constraint (because only one column target is supplied).

The constraint named integrity uses the CHECK clause to ensure that neither the book_id nor edition columns ever contain NULL values.

Finally, the book_exists constraint uses the FOREIGN KEY and REFERENCES clauses to verify that the book_id value always exists within the books table in the id column. Furthermore, since the CASCADE keyword is supplied for both the ON DELETE and ON UPDATE clauses, any modifications to the id column in the books table will also be made to the corresponding rows of the editions table, and any deletions from books will result in corresponding rows being deleted from editions.

Notice that both an implicit index named editions_pkey on the isbn column and an implicit trigger are created from these table constraints. The implicit index is used in the enforcement of the PRIMARY KEY constraint. The implicit trigger enforces the FOREIGN KEY constraint.

Adding a constraint

The ALTER TABLE command is intended to allow the addition of table constraints to existing tables. As of PostgreSQL 7.1.x, however, only the addition of CHECK and FOREIGN KEY constraints is supported.

Here is the syntax to add a constraint with ALTER TABLE:

  ALTER TABLE 
table

        ADD [ CONSTRAINT 
name
 ]
        { CHECK ( 
condition
 ) |
          FOREIGN KEY ( 
column
 [, ... ] )
                      REFERENCES 
table
 [ ( 
column
 [, ... ] ) ]
                      [ MATCH FULL | MATCH PARTIAL ]
                      [ ON DELETE 
action
 ]
                      [ ON UPDATE 
action
 ]
                      [ DEFERRABLE | NOT DEFERRABLE ]
                      [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
        }

Example 7-9 adds a FOREIGN KEY constraint to the Book Town books table's subject_id column. This constraint references the id column within the subjects table, and will ensure that no values are inserted or modified on the subject_id column with a value which cannot be found in the subjects table's id column.

Example 7-9. Adding a constraint to an existing table

booktown=# 
ALTER TABLE books ADD CONSTRAINT legal_subjects

booktown-# 
                  FOREIGN KEY (subject_id)

booktown-# 
                  REFERENCES subjects (id);

NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE

Removing a constraint

As of PostgreSQL 7.1.x, constraints may not be directly removed from a table. The only way to achieve the net effect of dropping a constraint is to create a copy of the table which is nearly identical to the original, with any unwanted constraints omitted. The data can then be copied from the original table to the new table, and the tables renamed using the ALTER TABLE command so that the new copy replaces the original table.

Warning

Be aware of who is connected to, and accessing, any tables that you wish to restructure with this work-around. Data should not be inserted or modified at any time in the middle of the operation; therefore, you may need to temporarily disallow connection to the database if it is a highly active table, make the modifications, and finally, restart the system when finished.

Example 7-10 demonstrates this work-around method for removing a constraint by effectively removing the legal_subjects FOREIGN KEY constraint from the books table (see Example 7-9). Notice that the books_id_pkey is removed before the new table is created, so that the new table can be created with an index named books_id_ pkey. This is not necessary, but for the sake of consistency we want to keep the primary key index name the same.

Example 7-10. Removing a constraint

booktown=# 
DROP INDEX books_id_pkey;

DROP
booktown=# 
CREATE TABLE new_books

booktown-# 
             (id integer CONSTRAINT books_id_pkey PRIMARY KEY,

booktown(# 
             title text NOT NULL,

booktown(# 
             author_id integer,

booktown(# 
             subject_id integer);

NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'
for table 'new_books'
CREATE
booktown=# 
INSERT INTO new_books SELECT * FROM books;

INSERT 0 15
booktown=# 
ALTER TABLE books RENAME TO old_books;

ALTER
booktown=# 
ALTER TABLE new_books RENAME TO books;

ALTER
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire