Constraints for ANSI SQL
This topic provides reference information about constraint compatibility between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can use this guide to understand the similarities and differences in how these two database systems handle various types of constraints, including check constraints, unique constraints, primary key constraints, and foreign key constraints.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
Unsupported |
SQL Server Usage
Column and table constraints are defined by the SQL standard and enforce relational data consistency. There are four types of SQL constraints: check constraints, unique constraints, primary key constraints, and foreign key constraints.
Check Constraints
CHECK (<Logical Expression>)
Check constraints enforce domain integrity by limiting the data values stored in table columns. They are logical Boolean expressions that evaluate to one of three values: TRUE
, FALSE
, and UNKNOWN
.
Note
Check constraint expressions behave differently than predicates in other query clauses. For example, in a WHERE
clause, a logical expression that evaluates to UNKNOWN
is functionally equivalent to FALSE
and the row is filtered out. For check constraints, an expression that evaluates to UNKNOWN
is functionally equivalent to TRUE
because the value is permitted by the constraint.
You can assign multiple check constraints to a single column. A single check constraint may apply to multiple columns. In this case, it is known as a table-level check constraint.
In ANSI SQL, check constraints can’t access other rows as part of the expression. In SQL Server, you can use user-defined functions in constraints to access other rows, tables, or even databases.
Unique Constraints
UNIQUE [CLUSTERED | NONCLUSTERED] (<Column List>)
Unique constraints should be used for all candidate keys. A candidate key is an attribute or a set of attributes such as columns that uniquely identify each row in the relation or table data.
Unique constraints guarantee that no rows with duplicate column values exist in a table.
A unique constraint can be simple or composite. Simple constraints are composed of a single column. Composite constraints are composed of multiple columns. A column may be a part of more than one constraint.
Although the ANSI SQL standard allows multiple rows having NULL values for unique constraints, in SQL Server, you can use a NULL value for only one row. Use a NOT NULL
constraint in addition to a unique constraint to disallow all NULL values.
To improve efficiency, SQL Server creates a unique index to support unique constraints. Otherwise, every INSERT
and UPDATE
would require a full table scan to verify there are no duplicates. The default index type for unique constraints is non-clustered.
Primary Key Constraints
PRIMARY KEY [CLUSTERED | NONCLUSTERED] (<Column List>)
A primary key is a candidate key serving as the unique identifier of a table row. Primary keys may consist of one or more columns. All columns that comprise a primary key must also have a NOT NULL constraint. Tables can have one primary key.
The default index type for primary keys is a clustered index.
Foreign Key Constraints
FOREIGN KEY (<Referencing Column List>) REFERENCES <Referenced Table>(<Referenced Column List>)
Foreign key constraints enforce domain referential integrity. Similar to check constraints, foreign keys limit the values stored in a column or set of columns.
Foreign keys reference columns in other tables, which must be either primary keys or have unique constraints. The set of values allowed for the referencing table is the set of values existing the referenced table.
Although the columns referenced in the parent table are indexed (since they must have either a primary key or unique constraint), no indexes are automatically created for the referencing columns in the child table. A best practice is to create appropriate indexes to support joins and constraint enforcement.
Foreign key constraints impose DML limitations for the referencing child table and for the parent table. The constraint’s purpose is to guarantee that no orphan rows with no corresponding matching values in the parent table exist in the referencing table. The constraint limits INSERT
and UPDATE
to the child table and UPDATE
and DELETE
to the parent table. For example, you can’t delete an order having associated order items.
Foreign keys support cascading referential integrity (CRI). CRI can be used to enforce constraints and define action paths for DML statements that violate the constraints. There are four CRI options:
-
NO ACTION — When the constraint is violated due to a DML operation, an error is raised and the operation is rolled back.
-
CASCADE — Values in a child table are updated with values from the parent table when they are updated or deleted along with the parent.
-
SET NULL — All columns that are part of the foreign key are set to NULL when the parent is deleted or updated.
-
SET DEFAULT — All columns that are part of the foreign key are set to their DEFAULT value when the parent is deleted or updated.
These actions can be customized independently of others in the same constraint. For example, a cascading constraint may have CASCADE
for UPDATE
, but NO ACTION
for UPDATE
.
Examples
The following example creates a composite non-clustered primary key.
CREATE TABLE MyTable ( Col1 INT NOT NULL, Col2 INT NOT NULL, Col3 VARCHAR(20) NULL, CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (Col1, Col2) );
The following example creates a table-level check constraint.
CREATE TABLE MyTable ( Col1 INT NOT NULL, Col2 INT NOT NULL, Col3 VARCHAR(20) NULL, CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (Col1, Col2), CONSTRAINT CK_MyTableCol1Col2 CHECK (Col2 >= Col1) );
The following example creates a simple non-null unique constraint.
CREATE TABLE MyTable ( Col1 INT NOT NULL, Col2 INT NOT NULL, Col3 VARCHAR(20) NULL, CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (Col1, Col2), CONSTRAINT UQ_Col2Col3 UNIQUE (Col2, Col3) );
The following example creates a foreign key with multiple cascade actions.
CREATE TABLE MyParentTable ( Col1 INT NOT NULL, Col2 INT NOT NULL, Col3 VARCHAR(20) NULL, CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (Col1, Col2) );
CREATE TABLE MyChildTable ( Col1 INT NOT NULL PRIMARY KEY, Col2 INT NOT NULL, Col3 INT NOT NULL, CONSTRAINT FK_MyChildTable_MyParentTable FOREIGN KEY (Col2, Col3) REFERENCES MyParentTable (Col1, Col2) ON DELETE NO ACTION ON UPDATE CASCADE );
For more information, see Unique Constraints and Check Constraints
MySQL Usage
Similar to SQL Server, Aurora MySQL supports all ANSI constraint types, except check.
Note
You can work around some of the functionality of CHECK (<Column>) IN (<Value List>)
using the SET
and ENUM
data types. For more information, see Data Types.
Unlike SQL Server, constraint names, or symbols in Aurora MySQL terminology, are optional. Identifiers are created automatically and are similar to SQL Server column constraints that are defined without an explicit name.
Unique Constraints
Unlike SQL Server, where unique constraints are objects supported by unique indexes, Aurora MySQL only provides unique indexes. A unique index is the equivalent to a SQL Server unique constraint.
As with SQL Server, unique indexes enforce distinct values for index columns. If a new row is added or an existing row is updated with a value that matches an existing row, an error is raised and the operation is rolled back.
Unlike SQL Server, Aurora MySQL permits multiple rows with NULL values for unique indexes.
Note
If a unique index consists of only one INT
type column, you can use the _rowid
alias to reference the index in SELECT
statements.
Primary Key Constraints
Similar to SQL Server, a primary key constraint in Aurora MySQL is a unique index where all columns are NOT NULL. Each table can have only one primary key. The name of the constraint is always PRIMARY
.
Primary keys in Aurora MySQL are always clustered. They can’t be configured as NON CLUSTERED
like SQL Server. For more information, see Indexes.
Applications can reference a primary key using the PRIMARY
alias. If a table has no primary key, which isn’t recommended, Aurora MySQL uses the first NOT NULL and unique index.
Note
Keep the primary key short to minimize storage overhead for secondary indexes. In Aurora MySQL, the primary key is clustered. Therefore, every secondary or nonclustered index maintains a copy of the clustering key as the row pointer. It is also recommended to create tables and declare the primary key first, followed by the unique indexes. Then create the non-unique indexes.
If a primary key consists of a single INTEGER
column, it can be referenced using the _rowid
alias in SELECT
commands.
Foreign Key Constraints
Note
MySQL doesn’t support foreign key constraints for partitioned tables. For more information, see Storage.
Aurora MySQL supports foreign key constraints for limiting values in a column, or a set of columns, of a child table based on their existence in a parent table.
Unlike SQL Server and contrary to the ANSI standard, Aurora MySQL allows foreign keys to reference nonunique columns in the parent table. The only requirement is that the columns are indexed as the leading columns of an index, but not necessarily a unique index.
Aurora MySQL supports cascading referential integrity actions using the ON UPDATE
and ON DELETE
clauses. The available referential actions are RESTRICT
, CASCADE
, SET NULL
, and NO ACTION
. The default action is RESTRICT
. RESTRICT
and NO ACTION
are synonymous.
Note
SET DEFAULT is supported by some other MySQL Server engines. Aurora MySQL uses the InnoDB engine exclusively, which doesn’t support SET DEFAULT
.
Note
Some database engines support the ANSI standard for deferred checks. NO ACTION
is a deferred check as opposed to RESTRICT
, which is immediate. In MySQL, foreign key constraints are always validated immediately. Therefore, NO ACTION
is the same as the RESTRICT
action.
Aurora MySQL handles foreign keys differently than most other engines in the following ways:
-
If there are multiple rows in the parent table that have the same values for the referenced foreign key, Aurora MySQL foreign key checks behave as if the other parent rows with the same key value don’t exist. For example, if a
RESTRICT
action is defined and a child row has several parent rows, Aurora MySQL doesn’t permit deleting them. -
If
ON UPDATE CASCADE
orON UPDATE SET NULL
causes a recursion and updates the same table that has been updated as part of the same cascade operation, Aurora MySQL treats it as if it was aRESTRICT
action. This effectively turns off self-referencingON UPDATE CASCADE
orON UPDATE SET NULL
operations to prevent potential infinite loops resulting from cascaded updates. A self-referencingON DELETE SET NULL
orON DELETE CASCADE
are allowed because there is no risk of an infinite loop. -
Cascading operations are limited to 15 levels deep.
Check Constraints
Standard ANSI check clauses are parsed correctly and don’t raise syntax errors. However, they are ignored and aren’t stored as part of the Aurora MySQL table definition.
Syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <Table Name> ( <Column Definition> [CONSTRAINT [<Symbol>]] PRIMARY KEY (<Column List>) | [CONSTRAINT [<Symbol>]] UNIQUE [INDEX|KEY] [<Index Name>] [<Index Type>] (<Column List>) | [CONSTRAINT [<Symbol>]] FOREIGN KEY [<Index Name>] (<Column List>) REFERENCES <Table Name> (<Column List>) [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT] [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT] );
Migration Considerations
-
Aurora MySQL doesn’t support check constraints. The engine parses the syntax for check constraints, but they are ignored.
-
Consider using triggers or stored routines to validate data values for complex expressions.
-
When using check constraints for limiting to a value list such as
CHECK (Col1 IN (1,2,3))
, consider using theENUM
orSET
data types. -
In Aurora MySQL, the constraint name (symbol) is optional, even for table constraints defined with the
CONSTRAINT
keyword. In SQL Server, it is mandatory. -
Aurora MySQL requires that both the child table and the parent table in foreign key relationship are indexed. If the appropriate index doesn’t exist, Aurora MySQL automatically creates one.
Examples
The following example creates a composite primary key.
CREATE TABLE MyTable ( Col1 INT NOT NULL, Col2 INT NOT NULL, Col3 VARCHAR(20) NULL, CONSTRAINT PRIMARY KEY (Col1, Col2) );
The following example creates a simple non-null unique constraint.
CREATE TABLE MyTable ( Col1 INT NOT NULL, Col2 INT NOT NULL, Col3 VARCHAR(20) NULL, CONSTRAINT PRIMARY KEY (Col1, Col2), CONSTRAINT UNIQUE (Col2, Col3) );
The following example creates a named foreign key with multiple cascade actions.
CREATE TABLE MyParentTable ( Col1 INT NOT NULL, Col2 INT NOT NULL, Col3 VARCHAR(20) NULL, CONSTRAINT PRIMARY KEY (Col1, Col2) );
CREATE TABLE MyChildTable ( Col1 INT NOT NULL PRIMARY KEY, Col2 INT NOT NULL, Col3 INT NOT NULL, FOREIGN KEY (Col2, Col3) REFERENCES MyParentTable (Col1, Col2) ON DELETE NO ACTION ON UPDATE CASCADE );
Summary
The following table identifies similarities, differences, and key migration considerations.
Feature | SQL Server | Aurora MySQL | Comments |
---|---|---|---|
Check constraints |
|
Not supported |
Aurora MySQL parses |
Unique constraints |
|
|
|
Primary key constraints |
|
|
|
Foreign key constraints |
|
|
|
Cascaded referential actions |
|
|
|
Indexing of referencing columns |
Not required |
Required |
If not specified, an index is created silently to support the constraint. |
Indexing of referenced columns |
|
Required |
Aurora MySQL doesn’t enforce uniqueness of referenced columns. |
Cascade recursion |
Not allowed, discovered at |
Not allowed, discovered at run time. |
For more information, see CREATE TABLE Statement