Add Constraint

Shambhu Rai 876 Reputation points

Hi Expert,

Is it possible to add constraint when table is loaded with data or we need to truncate the data

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,736 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,072 questions
{count} votes

Accepted answer
  1. Rahul Randive 1,651 Reputation points Microsoft Employee

    Hi @Shambhu Rai

    Thanks for your question.

    Yes, it's possible to add constraints to a table even after it has been loaded with data.

    However, this depends on the type of constraint being added.

    Also, adding a constraint to a large table with existing data may take a long time to complete and may impact performance during the operation.

    When you add a constraint to a table, the database engine validate that all existing data in the table conforms to the constraint.

    It is generally recommended to add constraints to a table before loading data into it.

    Thank you!

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Alberto Morillo 27,461 Reputation points MVP

    You can certainly add a constraint on an existing table populated with data.

    SQL Server 2022 (16.x) introduces resumable operations for adding table constraints for primary key and unique key constraints.

    Previous versions of SQL Server, the ALTER TABLE ADD CONSTRAINT operation can be executed with the ONLNE=ON option. However, the operation may take many hours for a large table to complete, and can consume a great number of resources. SQL 2022 has introduced resumable capabilities to ALTER TABLE ADD CONSTRAINT for users to pause the operation during a maintenance window, or to restart it from where it was interrupted during an execution failure, without restarting the operation from the beginning.

    It is a good idea to add a constraint to a big table during maintenance window.

    0 comments No comments

  2. Erland Sommarskog 77,496 Reputation points MVP

    In addition to the other posts: Yes, you can add constraint to a table with data. Note that adding the constraint will fail if there is data that violates the constraints. (This is actually overridable for CHECK and FOREIGN KEY constraints, but I strongly recommend against using that option. It's better to sort out the data data first.)

    0 comments No comments