Microsoft Learn / Documentation / SQL Server / Transact-SQL Syntax / ALTER TABLE ADD CONSTRAINT

ALTER TABLE ADD CONSTRAINT (Transact-SQL)

Adds one or more constraints to a table.

Applies to

Syntax

ALTER TABLE [database_name.schema_name].table_name ADD [ CONSTRAINT constraint_name ] { PRIMARY KEY ( column [ ,...n ] ) | FOREIGN KEY ( column [ ,...n ] ) REFERENCES referenced_table [ ( referenced_column [ ,...n ] ) ] [ NOT ENFORCED ] | UNIQUE ( column [ ,...n ] ) | CHECK ( logical_expression ) | DEFAULT ( constant_expression ) FOR column }

Parameters

Syntax
Parameters
Permissions
Return Values
Remarks
Examples

Syntax Details

The following list describes the syntax for the ALTER TABLE ADD CONSTRAINT statement:

  • database_name: The name of the database.
  • schema_name: The name of the schema.
  • table_name: The name of the table to which the constraint is added.
  • constraint_name: The name of the constraint. If not specified, a system-generated name is assigned.
  • PRIMARY KEY: Defines a primary key constraint. A table can have only one primary key.
  • FOREIGN KEY: Defines a foreign key constraint.
  • REFERENCES referenced_table[(referenced_column[...n])]: Specifies the table and optional columns that the foreign key references.
  • NOT ENFORCED: For foreign key constraints, specifies that the constraint is not enforced.
  • UNIQUE: Defines a unique constraint. All values in the constrained column(s) must be unique.
  • CHECK (logical_expression): Defines a check constraint. The logical_expression evaluates to TRUE or FALSE.
  • DEFAULT (constant_expression) FOR column: Defines a default constraint. The constant_expression is used as the default value for a column.

Parameter Details

This section details the parameters used in the ALTER TABLE ADD CONSTRAINT statement.

  • constraint_name: A user-defined name for the constraint. If omitted, SQL Server generates a name.
  • column: The column(s) to which the constraint applies.
  • referenced_table: The table referenced by a foreign key constraint.
  • referenced_column: The column(s) in the referenced table.
  • logical_expression: A Boolean expression that must evaluate to TRUE for the constraint to be satisfied.
  • constant_expression: A literal value, variable, or function that returns a constant value.

Permissions

To execute ALTER TABLE ADD CONSTRAINT, the user must have:

  • ALTER permission on the table.
  • CONTROL permission on the table.
  • db_ddladmin fixed database role membership.
  • db_owner fixed database role membership.

Return Values

ALTER TABLE ADD CONSTRAINT returns no specific value. Success is indicated by the absence of an error.

Remarks

Constraints are rules that enforce data integrity. Common constraints include:

  • Primary Key: Uniquely identifies each record in a table.
  • Foreign Key: Ensures referential integrity between tables.
  • Unique Constraint: Ensures that all values in a column are unique.
  • Check Constraint: Limits the range of values that can be placed in a column.
  • Default Constraint: Provides a default value when no value is specified for a column.
Note: When adding a PRIMARY KEY or UNIQUE constraint to a table that already contains duplicate values in the constrained column(s), the statement will fail.
Tip: For performance, consider adding constraints to tables with minimal data first, or disabling constraints and enabling them after data loading.

Examples

Example 1: Adding a Primary Key Constraint


ALTER TABLE Production.Product
ADD CONSTRAINT PK_Product_ProductID PRIMARY KEY (ProductID);

Example 2: Adding a Foreign Key Constraint


ALTER TABLE Sales.SalesOrderDetail
ADD CONSTRAINT FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
    FOREIGN KEY (SalesOrderID)
    REFERENCES Sales.SalesOrderHeader (SalesOrderID);

Example 3: Adding a Unique Constraint


ALTER TABLE HumanResources.Employee
ADD CONSTRAINT UQ_Employee_NationalIDNumber UNIQUE (NationalIDNumber);

Example 4: Adding a Check Constraint


ALTER TABLE Production.Product
ADD CONSTRAINT CK_Product_ListPrice
    CHECK (ListPrice >= 0.00 AND ListPrice <= 10000.00);

Example 5: Adding a Default Constraint


ALTER TABLE Production.Product
ADD CONSTRAINT DF_Product_SellStartDate
    DEFAULT GETDATE() FOR SellStartDate;