Add Constraint

Shambhu Rai 1,406 Reputation points
2023-05-23T20:55:56.6+00:00

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.
12,710 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions
Accepted answer
  1. Rahul Randive 8,271 Reputation points Microsoft Employee
    2023-05-23T21:24:58.9333333+00:00

    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

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2023-05-23T21:15:57.31+00:00

    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
  2. Erland Sommarskog 101K Reputation points MVP
    2023-05-23T21:32:27.8733333+00:00

    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