CONSTRAINT CLAUSE

What is a constraint?

Types of Constraints

Column constraints and table constraints have the same function; the difference is in where you specify them.

  1. a column-level constraint
    Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow.
  2. a table-level constraint
    Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table.

Below, we have a table where the constraints are given at the column level

 

Constraint Type Description
NOT NULL Specifies that this column cannot hold NULL values (constraints of this type are not nameable).
PRIMARY KEY Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.
Note: If you attempt to add a primary key using ALTER TABLE and any of the columns included in the primary key contain null values, an error will be generated, and the primary key will not be added. See ALTER TABLE statement for more information.
UNIQUE Specifies that values in the column must be unique.
FOREIGN KEY Specifies that the values in the column must correspond to values in a referenced primary key or unique key column or that they are NULL.
CHECK Specifies rules for values in the column.

Below, we have a table where the constraints are given at the table level

 

Constraint Type Description
PRIMARY KEY Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.
UNIQUE Specifies that values in the columns must be unique.
FOREIGN KEY Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.
Note: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.
CHECK Specifies a wide range of rules for values in the table.

 

Primary key constraints

A primary key defines the set of columns that uniquely identifies rows in a table.

When you create a primary key constraint, none of the columns included in the primary key can have NULL constraints; that is, they must not permit NULL values.

ALTER TABLE ADD PRIMARY KEY allows you to include existing columns in a primary key if they were first defined as NOT NULL. NULL values are not allowed. If the column(s) contain NULL values, the system will not add the primary key constraint. See ALTER TABLE statement for more information.

A table can have at most one PRIMARY KEY constraint.

 

Unique constraints

A UNIQUE constraint defines a set of columns that uniquely identify rows in a table only if all the key values are not NULL. If one or more key parts are NULL, duplicate keys are allowed.

For example, if there is a UNIQUE constraint on col1 and col2 of a table, the combination of the values held by col1 and col2 will be unique as long as these values are not NULL. If one of col1 and col2 holds a NULL value, there can be another identical row in the table.

A table can have multiple UNIQUE constrainTS

 

Foreign Key

Foreign keys ensure database integrity by linking columns in one table to the primary key or unique constraint in another. They must precisely match in terms of column types. When creating table-level foreign key constraints, each column can only be used once. The ReferencesSpecification, specifying columns in the referenced table, must match a unique or primary key constraint. If no column list is provided and the referenced table lacks a primary key, an exception is thrown.

A foreign key is satisfied when there’s a matching value in the referenced unique or primary key column. If the foreign key has multiple columns and any are NULL, the entire foreign key value is considered NULL. To avoid potential issues per the SQL-92 standard, it’s recommended to add NOT NULL constraints to all foreign key columns.

Foreign key constraints and DML

When you insert into or update a table with an enabled foreign key constraint, Derby checks that the row does not violate the foreign key constraint by looking up the corresponding referenced key in the referenced table. If the constraint is not satisfied, Derby rejects the insert or update with a statement exception.

Let us take a look at constrainsts in action

link to the manual

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top