Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Table Constraints

Oracle Usage

Oracle provides six types of constraints to enforce data integrity on table columns. Constraints ensure data inserted into tables is controlled and satisfies logical requirements.

Oracle Integrity Constraint Types

Oracle Constraint Creation

You can create new constraints in two ways.

Use the following syntax to specify Oracle constraints:

Privileges

You need privileges on the table where constrains are created and, in case of foreign key constraints, you need the REFERENCES privilege on the referenced table.

PRIMARY KEY Constraints

A unique identifier for each record in a database table can appear only once and can’t contain NULL values. A table can only have one primary key.

When you create a primary key constraint inline, you can specify only the PRIMARY KEY keyword. When you create the constraint out-of-line, you must specify one column or a combination of columns.

Creating a new primary key constraint also implicitly creates a unique index on the primary key column if no index already exists. When dropping a primary key constraint, the system-generated index is also dropped. If a user defined index was used, the index isn’t dropped.

Examples

Create an inline primary key using a system-generated primary key constraint name.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25));

Create an inline primary key using a user-specified primary key constraint name.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER CONSTRAINT PK_EMP_ID PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25));

Create an out-of-line primary key.

CREATE TABLE EMPLOYEES(
  EMPLOYEE_ID NUMBER,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25));
  CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID));

Add a primary key to an existing table.

ALTER TABLE SYSTEM_EVENTS
  ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EVENT_CODE, EVENT_TIME);

FOREIGN KEY Constraints

Foreign key constraints identify the relationship between column records defined with a foreign key constraint and a referenced primary key or a unique column. The main purpose of a foreign key is to enforce that the values in table A also exist in table B as referenced by the foreign key.

A referenced table is known as a parent table. The table on which the foreign key was created is known as a child table. Foreign keys created in child tables generally reference a primary key constraint in a parent table.

Limitations

Foreign keys can’t be created on columns defined with the following data types: LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE.

Composite foreign key constraints comprised from multiple columns can’t have more than 32 columns.

Foreign key constraints can’t be created in a CREATE TABLE statement with a subquery clause.

A referenced primary key or unique constraint on a parent table must be created before the foreign key creation command.

ON DELETE Clause

The ON DELETE clause specifies the effect of deleting values from a parent table on the referenced records of a child table. If the ON DELETE clause isn’t specified, Oracle doesn’t allow deletion of referenced key values in a parent table that has dependent rows in the child table.

Examples

Create an inline foreign key with a user-defined constraint name.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25) ,
  DEPARTMENT_ID REFERENCES DEPARTMENTS(DEPARTMENT_ID));

Create an out-of-line foreign key with a system-generated constraint name.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25),
  DEPARTMENT_ID NUMBER,
  CONSTRAINT FK_FEP_ID
  FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID));

Create a foreign key using the ON DELETE CASCADE clause.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25),
  DEPARTMENT_ID NUMBER,
  CONSTRAINT FK_FEP_ID
  FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
  ON DELETE CASCADE);

Add a foreign key to an existing table.

ALTER TABLE EMPLOYEES
  ADD CONSTRAINT FK_FEP_ID
  FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID);

UNIQUE Constraints

A unique constraint is similar to a primary key constraint. It specifies that the values in a single column, or combination of columns, must be unique and can’t repeat in multiple rows.

The main difference from primary key constraint is that a unique constraint can contain NULL values. NULL values in multiple rows are also supported provided the combination of values is unique.

Limitations

A unique constraint can’t be created on columns defined with the following data types: LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE.

A unique constraint comprised from multiple columns can’t have more than 32 columns.

Primary key and unique constraints can’t be created on the same column or columns.

Example

Create an inline unique Constraint.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25) CONSTRAINT UNIQ_EMP_EMAIL UNIQUE,
  DEPARTMENT_ID NUMBER);

Check Constraints

Check constraints are used to validate values in specific columns that meet specific criteria or conditions. For example, you can use a check constraint on an EMPLOYEE_EMAIL column to validate that each record has an @aws.com suffix. If a record fails the check validation, an error is raised and the record isn’t inserted.

Using a check constraint can help transfer some of the logical integrity validation from the application to the database.

When you create a check constraint as inline, it can only be defined on a specific column. When using the out-of-line method, the check constraint can be defined on multiple columns.

Limitations

Check constraints can’t perform validation on columns of other tables.

Check constraints can’t be used with functions that aren’t deterministic (for example, CURRENT_DATE).

Check constraints can’t be used with user-defined functions.

Check constrains can’t be used with pseudo columns such as: CURRVAL, NEXTVAL, LEVEL, or ROWNUM.

Example

Create an inline check constraint that uses a regular expression to validate the email suffix of inserted rows contains @aws.com.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25)
  CHECK(REGEXP_LIKE (EMAIL, '^[A-Za-z]+@aws.com?{1,3}$')),
  DEPARTMENT_ID NUMBER);

Not Null Constraints

A not null constraint prevents a column from containing any null values. To enable the not null constraint, make sure that you specify the NOT NULL keyword during table creation (inline only). Permitting null values is the default if NOT NULL isn’t specified.

Example

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20) NOT NULL,
  LAST_NAME VARCHAR2(25) NOT NULL,
  EMAIL VARCHAR2(25),
  DEPARTMENT_ID NUMBER);

Referential Constraints

Referential constraints define a relationship between a column of type REF and the object it references. The REF constraint can be created both inline and out-of-line. Both methods permit defining a scope constraint, a row identifier constraint, or a referential integrity constraint based on the REF column.

Examples

Create a new Oracle type object.

CREATE TYPE DEP_TYPE AS OBJECT (
  DEP_NAME VARCHAR2(60),
  DEP_ADDRESS VARCHAR2(300));

Create a table based on the previously created type object.

CREATE TABLE DEPARTMENTS_OBJ_T OF DEP_TYPE;

Create the EMPLOYEES table with a reference to the previously created DEPARTMENTS table that is based on the DEP_TYPE object:

CREATE TABLE EMPLOYEES (
  EMP_NAME VARCHAR2(60),
  EMP_EMAIL VARCHAR2(60),
  EMP_DEPT REF DEPARTMENT_TYP REFERENCES DEPARTMENTS_OBJ_T);

Special Constraint States

Oracle provides granular control of database constraint enforcement. For example, you can disable constraints temporarily while making modifications to table data.

Constraint states can be defined using the CREATE TABLE or ALTER TABLE statements. The following constraint states are supported:

Examples

Create a unique constraint with a state of DEFERRABLE.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  FIRST_NAME VARCHAR2(20),
  LAST_NAME VARCHAR2(25),
  EMAIL VARCHAR2(25) CONSTRAINT UNIQ_EMP_EMAIL UNIQUE DEFERRABLE,
  DEPARTMENT_ID NUMBER);

Modify the state of the constraint to ENABLE NOVALIDATE.

ALTER TABLE EMPLOYEES
  ADD CONSTRAINT CHK_EMP_NAME CHECK(FIRST_NAME LIKE 'a%')
  ENABLE NOVALIDATE;

Using Existing Indexes to Enforce Constraint Integrity

Primary key and unique constraints can be created based on an existing index to enforce the constraint integrity instead of implicitly creating a new index during constraint creation.

Example

Create a unique constraint based on an existing index.

CREATE UNIQUE INDEX IDX_EMP_ID ON EMPLOYEES(EMPLOYEE_ID);

ALTER TABLE EMPLOYEES
  ADD CONSTRAINT PK_CON_UNIQ
  PRIMARY KEY(EMPLOYEE_ID) USING INDEX IDX_EMP_ID;

For more information, see CREATE TABLE in the Oracle documentation.

MySQL Usage

MySQL supports the following types of table constraints:

Similar to constraint declaration in Oracle, in MySQL you can create constraints in-line or out-of-line when you specify table columns.

You can specify MySQL constraints using CREATE or ALTER TABLE. Views aren’t supported.

You need privileges on the table in which constrains are created. For foreign key constraints, you need the REFERENCES privilege.

Primary Key Constraints

Primary key constraints uniquely identify each record and can’t contain a NULL value.

Primary key constraint marks the column on which the table’s heap is sorted (in the InnoDB storage engine, like Oracle IOT).

Primary key constraint uses the same ANSI SQL syntax as Oracle.

You can create a primary key constraint on a single column, or on multiple columns (composite primary keys), as the only PRIMARY KEY in a table.

Primary key constraint creates a unique B-tree index automatically on the column, or group of columns, marked as the primary key of the table.

Constraint names can be generated automatically by MySQL. If a name is explicitly specified during constraint creation, the constraint name is PRIMARY.

Examples

Create an inline primary key constraint with a system-generated constraint name.

CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMERIC PRIMARY KEY,
  FIRST_NAME VARCHAR(20),
  LAST_NAME VARCHAR(25),
  EMAIL VARCHAR(25));

Create an out-of-line primary key constraint. For both examples, the constraint name is PRIMARY.

CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID NUMERIC,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25),
    EMAIL VARCHAR(25),
    CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID));

or

CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID NUMERIC,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25)
    EMAIL VARCHAR(25),
    CONSTRAINT PRIMARY KEY (EMPLOYEE_ID));

Add a primary key constraint to an existing table.

ALTER TABLE SYSTEM_EVENTS
    ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EVENT_CODE, EVENT_TIME);

or

ALTER TABLE SYSTEM_EVENTS
    ADD CONSTRAINT PRIMARY KEY (EVENT_CODE, EVENT_TIME);

or

ALTER TABLE SYSTEM_EVENTS
    ADD PRIMARY KEY (EVENT_CODE, EVENT_TIME);

Drop the primary key.

ALTER TABLE SYSTEM_EVENTS DROP PRIMARY KEY;

Foreign Key Constraints

Important notes about foreign key constraints:

ON DELETE clause

MySQL provides four options to handle cases where data is deleted from the parent table and a child table is referenced by a FOREIGN KEY constraint. By default, without specifying any additional options, MySQL uses the NO ACTION method and raises an error if the referencing rows still exist when the constraint is verified.

ON UPDATE clause

Handle updates on FOREIGN KEY columns is also available using the ON UPDATE clause, which shares the same options as the ON DELETE clause:

Examples

Create an out-of-line foreign key constraint with a system-generated constraint name.

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMERIC PRIMARY KEY,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25),
    EMAIL VARCHAR(25),
    DEPARTMENT_ID NUMERIC,
    CONSTRAINT FK_FEP_ID FOREIGN KEY(DEPARTMENT_ID)
        REFERENCES DEPARTMENTS(DEPARTMENT_ID));

Create a foreign key using the ON DELETE CASCADE clause.

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMERIC PRIMARY KEY,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25),
    EMAIL VARCHAR(25),
    DEPARTMENT_ID NUMERIC,
    CONSTRAINT FK_FEP_ID FOREIGN KEY(DEPARTMENT_ID)
    REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE CASCADE);

Add a foreign key to an existing table.

ALTER TABLE EMPLOYEES
    ADD CONSTRAINT FK_FEP_ID
    FOREIGN KEY(DEPARTMENT_ID)
    REFERENCES DEPARTMENTS(DEPARTMENT_ID);

UNIQUE Constraints

Important notes about unique constraints:

Example

Create an inline unique constraint ensuring uniqueness of values in the email column.

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMERIC PRIMARY KEY,
    FIRST_NAME VARCHAR(20),
    LAST_NAME VARCHAR(25),
    EMAIL VARCHAR(25) UNIQUE,
    DEPARTMENT_ID NUMERIC);

Disable Integration Check

In MySQL, you don’t have an option to DISABLE the integration check, but there is a session variable for disabling checks at the session level.

The following example turns on integration checks in the session.

SET FOREIGN_KEY_CHECKS=1;

The following example turns off integration checks in the session.

SET FOREIGN_KEY_CHECKS=0;

Unique MySQL Constraints

Summary

For more information, see How MySQL Deals with Constraints, and FOREIGN KEY Constraints in the MySQL documentation.