EMS logo

Produkt Navigation

Wählen Sie Ihr DB-Tool aus

Unsere Partnerschaften

Microsoft Certified Partner
Oracle Certified Partner
Embarcadero Technology Partner

SQL Artikel

Alle SQL Artikel

Bruce Momjian
SQL Constraints With PostgreSQL

POSTGRESQL is an object-relational database under active development on the Internet. You can learn more by visiting http://www.postgresql.org.

Constraints keep user data constrained, thereby helping to prevent invalid data from being entered into the database. Defining a data type for a column is a constraint in itself. For example, a column of type DATE constrains the column to valid dates.

This article covers a variety of constraints. A DEFAULT column value can be specified at table creation. Constraints are defined at table creation in a similar way.

NOT NULL

The constraint NOT NULL prevents NULL values from appearing in a column. The following figure shows the creation of a table with a NOT NULL constraint.  

        test=> CREATE TABLE not_null_test (
        test(>                             col1 INTEGER, 
        test(>                             col2 INTEGER NOT NULL
        test(>                            );
        CREATE
        test=> INSERT INTO not_null_test
        test-> VALUES (1, NULL);
        ERROR:  ExecAppend: Fail to add null value in not null attribute col2
        test=> INSERT INTO not_null_test (col1)
        test-> VALUES (1);
        ERROR:  ExecAppend: Fail to add null value in not null attribute col2
        test=> INSERT INTO not_null_test VALUES (1, 1);
        INSERT 174368 1
        test=> UPDATE  not_null_test SET col2 = NULL;
        ERROR:  ExecReplace: Fail to add null value in not null attribute col2
 

Insertion of a NULL value, or an INSERT that would set col2 to NULL, causes the INSERT to fail. As shown in the figure, an UPDATE of a NULL value also fails.

This figure adds a DEFAULT value for col2.  

        test=> CREATE TABLE not_null_with_default_test (
        test(>                                          col1 INTEGER,
        test(>                                          col2 INTEGER NOT NULL DEFAULT 5
        test(>                                         );
        CREATE
        test=> INSERT INTO not_null_with_default_test (col1) 
        test-> VALUES (1);
        INSERT 148520 1
        test=> SELECT * 
        test-> FROM not_null_with_default_test;
         col1 | col2 
        ------+------
            1 |    5
        (1 row)
 

This addition permits INSERTs that do not specify a value for col2.

UNIQUE

The UNIQUE constraint prevents duplicate values from appearing in the column. It is implemented by creating a unique index on a column. As indicated in next figure, UNIQUE prevents duplicates.  

        test=> CREATE TABLE uniquetest (col1 INTEGER UNIQUE);
        NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'uniquetest_col1_key' for table 'uniquetest'
        CREATE
        test=> \d uniquetest
               Table "uniquetest"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col1      | integer | 
        Index: uniquetest_col1_key 
         
        test=> INSERT INTO uniquetest VALUES (1);
        INSERT 148620 1
        test=> INSERT INTO uniquetest VALUES (1);
        ERROR:  Cannot insert a duplicate key into unique index uniquetest_col1_key
        test=> INSERT INTO uniquetest VALUES (NULL);
        INSERT 148622 1
        test=> INSERT INTO uniquetest VALUES (NULL);
        INSERT
 

CREATE TABLE displays the name of the unique index created. The figure also shows that multiple NULL values can be inserted into a UNIQUE column.

If a UNIQUE constraint consists of more than one column, UNIQUE cannot be used as a column constraint. Instead, you must use a separate UNIQUE line to specify the columns that make up the constraint. This approach creates a UNIQUE table constraint.

This figure shows a multicolumn UNIQUE constraint.  

        test=> CREATE TABLE uniquetest2 (
        test(>                           col1 INTEGER, 
        test(>                           col2 INTEGER, 
        test(>                           UNIQUE (col1, col2)
        test(>                          );
        NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'uniquetest2_col1_key' for table 'uniquetest2'
 

While col1 or col2 themselves may not be unique, the constraint requires the combination of col1 and col2 to be unique. For example, in a table that contains the driver's license numbers of people in various states, two people in different states might have the same license number, but the combination of their state and license number should always be unique.

PRIMARY KEY

The PRIMARY KEY constraint, which marks the column that uniquely identifies each row, is a combination of UNIQUE and NOT NULL constraints. With this type of constraint, UNIQUE prevents duplicates, and NOT NULL prevents NULL values in the column. The next figure shows the creation of a PRIMARY KEY column.  

        test=> CREATE TABLE primarytest (col INTEGER PRIMARY KEY);
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest_pkey' for table 'primarytest'
        CREATE
        test=> \d primarytest 
              Table "primarytest"
         Attribute |  Type   | Modifier 
        -----------+---------+----------
         col       | integer | not null
        Index: primarytest_pkey
 

Notice that an index is created automatically, and the column is defined as NOT NULL.

Just as with UNIQUE, a multicolumn PRIMARY KEY constraint must be specified on a separate line. In the next figure, col1 and col2 are combined to form the primary key.  

        test=> CREATE TABLE primarytest2 (
        test(>                            col1 INTEGER, 
        test(>                            col2 INTEGER, 
        test(>                            PRIMARY KEY(col1, col2)
        test(>                           );
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest2_pkey' for table 'primarytest2'
        CREATE
 

A table cannot have more than one PRIMARY KEY specification. Primary keys have special meaning when using foreign keys, which are covered in the next section.

Foreign Key/REFERENCES

Foreign keys are more complex than primary keys. Primary keys make a column UNIQUE and NOT NULL. Foreign keys, on the other hand, constrain data based on columns in other tables. They are called foreign keys because the constraints are foreign--that is, outside the table.

For example, suppose a table contains customer addresses, and part of each address is a United States two-character state code. If a table held all valid state codes, a foreign key constraint could be created to prevent a user from entering invalid state codes.

Figure  shows the creation of a primary key/foreign key relationship.  

        test=>  CREATE TABLE statename (code CHAR(2) PRIMARY KEY,
        test(>                          name  CHAR(30) 
        test(> );
        CREATE
        test=> INSERT INTO statename VALUES ('AL', 'Alabama');
        INSERT 18934 1
        ... 
         
        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2) REFERENCES statename,
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE
 

Foreign key constraints are created by using REFERENCES to refer to the primary key of another table. Foreign keys link the tables together and prevent the insertion or updating of invalid data.

This figure shows how foreign keys constrain column values.  

        test=> INSERT INTO customer (state) 
        test-> VALUES ('AL');
        INSERT 148732 1
        test=> INSERT INTO customer (state)
        test-> VALUES ('XX');
        ERROR:  <unnamed> referential integrity violation - key referenced from customer not found in statename
 

Here AL is a primary key value in statename, so the INSERT is accepted. XX is not a primary key value in statename, so the INSERT is rejected by the foreign key constraint.

The next figure shows the creation of the company tables using primary and foreign keys.  

        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER PRIMARY KEY,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2),
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE
        test=> CREATE TABLE employee (
        test(>                        employee_id INTEGER PRIMARY KEY,
        test(>                        name        CHAR(30),
        test(>                        hire_date   DATE
        test(> );
        CREATE
        test=> CREATE TABLE part (
        test(>                    part_id     INTEGER PRIMARY KEY,
        test(>                    name        CHAR(30),
        test(>                    cost        NUMERIC(8,2),
        test(>                    weight      FLOAT
        test(> );
        CREATE
        test=> CREATE TABLE salesorder (
        test(>                          order_id      INTEGER,
        test(>                          customer_id   INTEGER REFERENCES customer,
        test(>                          employee_id   INTEGER REFERENCES employee,
        test(>                          part_id       INTEGER REFERENCES part,
        test(>                          order_date    DATE,
        test(>                          ship_date     DATE, 
        test(>                          payment       NUMERIC(8,2)
        test(> );
        CREATE
 

A variety of foreign key options are discussed next that make foreign keys even more powerful.

Modification of Primary Key Row

If a foreign key constraint references a row as its primary key, and the primary key row is updated or deleted, then the default foreign key action is to prevent the operation. The foreign key options ON UPDATE and ON DELETE , however, allow a different action to be taken. Figure  shows how these options work.  

        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2) REFERENCES statename 
        test(>                                            ON UPDATE CASCADE 
        test(>                                            ON DELETE SET NULL,
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE
 

The new customer table's ON UPDATE CASCADE specifies that if statename's primary key is updated, customer.state should be updated with the new value as well. The foreign key ON DELETE SET NULL option specifies that if someone tries to delete a statename row that is referenced by another table, the delete operation should set the foreign key to NULL.

The ON UPDATE and ON DELETE options can have the following actions:

NO ACTION
UPDATEs and DELETEs to the primary key are prohibited if referenced by a foreign key row. This is the default.
CASCADE
UPDATEs to the primary key update all foreign key columns that reference it. DELETEs on the primary key cause the deletion of all foreign key rows that reference it.
SET NULL
UPDATEs and DELETEs to the primary key row cause the foreign key to be set to NULL.
SET DEFAULT
UPDATEs and DELETEs to the primary key row cause the foreign key to be set to its DEFAULT.
Figure  illustrates the use of the CASCADE and NO ACTION rules.  

        test=> CREATE TABLE primarytest (col INTEGER PRIMARY KEY);
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest_pkey' for table 'primarytest'
        CREATE
        test=> CREATE TABLE foreigntest (
        test(>                           col2 INTEGER REFERENCES primarytest
        test(>                           ON UPDATE CASCADE 
        test(>                           ON DELETE NO ACTION
        test(>                          );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> INSERT INTO primarytest values (1);
        INSERT 148835 1
        test=> INSERT INTO foreigntest values (1);
        INSERT 148836 1
        test=>
        test=> -- CASCADE UPDATE is performed
        test=>
        test=> UPDATE primarytest SET col = 2;
        UPDATE 1
        test=> SELECT * FROM foreigntest;
         col2 
        ------
            2
        (1 row) 
         
        test=> 
        test=> -- NO ACTION prevents deletion
        test=>
        test=> DELETE FROM primarytest;
        ERROR:  <unnamed> referential integrity violation - key in primarytest still referenced from foreigntest
        test=>
        test=> -- By deleting the foreign key first, the DELETE succeeds
        test=>
        test=> DELETE FROM foreigntest;
        DELETE 1
        test=> DELETE FROM primarytest;
        DELETE 1
 

First, primarytest, which was used a previous figure, is recreated. Then a foreigntest table with ON UPDATE CASCADE and ON DELETE NO ACTION is created. NO ACTION is the default, so ON DELETE NO ACTION was not required. Next, a single row is inserted into each table, and an UPDATE on primarytest cascades to UPDATE foreigntest. The primarytest row cannot be deleted unless the foreign key row is deleted first. Foreign key actions offer you great flexibility in controlling how primary key changes affect foreign key rows.

Multicolumn Primary Keys

To specify a multicolumn primary key, it was necessary to use PRIMARY KEY on a separate line in the CREATE TABLE statement. Multicolumn foreign keys have the same requirement. Using primarytest2 from a previous figure, this figure shows how to create a multicolumn foreign key.  

        test=> CREATE TABLE primarytest2 (
        test(>                            col1 INTEGER, 
        test(>                            col2 INTEGER, 
        test(>                            PRIMARY KEY(col1, col2)
        test(>                           );
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest2_pkey' for table 'primarytest2'
        CREATE
        test=> CREATE TABLE foreigntest2 (col3 INTEGER, 
        test(>                            col4 INTEGER,
        test(>                            FOREIGN KEY (col3, col4) REFERENCES primarytest2
        test->                          );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
 

FOREIGN KEY (col, ...) must be used to label any multicolumn foreign key table constraints.

Handling NULL Values in the Foreign Key

A NULL value cannot reference a primary key. A single-column foreign key is either NULL or matches a primary key. In a multicolumn foreign key, sometimes only part of a foreign key can be NULL. The default behavior allows some columns in a multicolumn foreign key to be NULL and others to be not NULL.

Using MATCH FULL in a multicolumn foreign key constraint requires all columns in the key to be NULL or all columns to be not NULL. This figure illustrates this case.  

        test=> INSERT INTO primarytest2 
        test-> VALUES (1,2);
        INSERT 148816 1
        test=> INSERT INTO foreigntest2
        test-> VALUES (1,2);
        INSERT 148817 1
        test=> UPDATE foreigntest2
        test-> SET col4 = NULL;
        UPDATE 1
        test=> CREATE TABLE matchtest (
        test(>                    col3 INTEGER,
        test(>                    col4 INTEGER,
        test(>                    FOREIGN KEY (col3, col4) REFERENCES primarytest2 
        test(>                                             MATCH FULL
        test(>                        );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> UPDATE matchtest 
        test-> SET col3 = NULL, col4 = NULL;
        UPDATE 1
        test=> UPDATE matchtest
        test-> SET col4 = NULL;
        ERROR:  <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
 

First, the tables from a previous figure are used to show that the default allows one column of a foreign key to be set to NULL. Next, the table matchtest is created with the MATCH FULL foreign key constraint option. MATCH FULL allows all key columns to be set to NULL, but rejects the setting of only some multicolumn key values to NULL.

Frequency of Foreign Key Checking

By default, foreign key constraints are checked at the end of each INSERT, UPDATE , and DELETE query. Thus, if you perform a set of complex table modifications, the foreign key constraints must remain valid at all times. For example, using the tables from a previous figure, if a new state is added and then a new customer in the new state is inserted, the new state must be added to statename before the customer is added to customer.

In some cases, it may not be possible to keep foreign key constraints valid between queries. For example, if two tables are foreign keys for each other, it may not be possible to INSERT into one table without having the other table row already present. A solution is to use the DEFERRABLE foreign key option and SET CONSTRAINTS so that foreign key constraints are checked only at transaction commit. With this approach, a multiquery transaction can make table modifications that violate foreign key constraints inside the transaction as long as the foreign key constraints are met at transactions commit. The next figure is a contrived example of this case; the proper way to perform this query is to INSERT into primarytest first, then INSERT into defertest.  

        test=> CREATE TABLE defertest( 
        test(>                        col2 INTEGER REFERENCES primarytest 
        test(>                                     DEFERRABLE
        test(> );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> BEGIN;
        BEGIN
        test=> -- INSERT is attempted in non-DEFERRABLE mode
        test=>
        test=> INSERT INTO defertest VALUES (5);
        ERROR:  <unnamed> referential integrity violation - key referenced from defertest not found in primarytest
        test=> COMMIT;
        COMMIT
        test=> BEGIN;
        BEGIN
        test=> -- all foreign key constraints are set to DEFERRED
        test=>
        test=> SET CONSTRAINTS ALL DEFERRED;
        SET CONSTRAINTS
        test=> INSERT INTO defertest VALUES (5);
        INSERT 148946 1
        test=> INSERT INTO primarytest VALUES (5);
        INSERT 148947 1
        test=> COMMIT;
        COMMIT
 

In complex situations, such reordering might not be possible, so DEFERRABLE and SET CONSTRAINTS should be used to defer foreign key constraints. A foreign key may also be configured as INITIALLY DEFERRED, causing the constraint to be checked only at transaction commit by default.

You can name constraints if desired. The constraint names will appear in constraint violation messages and can be used by SET CONSTRAINTS. See the CREATE_TABLE and SET manual pages for more information.

CHECK

The CHECK constraint enforces column value restrictions. Such constraints can restrict a column, for example, to a set of values, only positive numbers, or reasonable dates. Figure  shows an example of CHECK constraints using a modified version of the friend table from Figure  , page  .  

        test=> CREATE TABLE friend2 (
        test(>              firstname CHAR(15),
        test(>              lastname  CHAR(20),
        test(>              city      CHAR(15),
        test(>              state     CHAR(2)      CHECK (length(trim(state)) = 2),
        test(>              age       INTEGER      CHECK (age >= 0),
        test(>              gender    CHAR(1)      CHECK (gender IN ('M','F')),
        test(>              last_met  DATE         CHECK (last_met BETWEEN '1950-01-01'
        test(>                                            AND CURRENT_DATE),
        test(>              CHECK (upper(trim(firstname)) != 'ED' OR
        test(>                     upper(trim(lastname)) != 'RIVERS')
        test(> );
        CREATE
        test=> INSERT INTO friend2 
        test-> VALUES ('Ed', 'Rivers', 'Wibbleville', 'J', -35, 'S', '1931-09-23');
        ERROR:  ExecAppend: rejected due to CHECK constraint friend2_last_met
 

This figure has many CHECK clauses:

state
Forces the column to be two characters long. CHAR() pads the field with spaces, so state must be trim() -ed of trailing spaces before length() is computed.
age
Forces the column to hold only positive values.
gender
Forces the column to hold either M or F.
last_met
Forces the column to include dates between January 1, 1950, and the current date.
table
Forces the table to accept only rows where firstname is not ED or lastname is not RIVERS. The effect is to prevent Ed Rivers from being entered into the table. His name will be rejected if it is in uppercase, lowercase, or mixed case. This restriction must be implemented as a table-level CHECK constraint. Comparing firstname to ED at the column level would have prevented all EDs from being entered, which was not desired. Instead, the desired restriction is a combination of firstname and lastname.
Next, the example tries to INSERT a row that violates all CHECK constraints. Although the CHECK failed on the friend2_last_met constraint, if that were corrected, the other constraints would prevent the insertion. By default, CHECK allows NULL values.

Quelle: www.postgresql.org

twitterfacebook