In SQL(SQL Server), adding constraints to a table is a common practice to enforce data integrity and ensure that the data in the table adheres to certain rules or conditions. Constraints help maintain the accuracy and reliability of the database by preventing the insertion of invalid or inconsistent data. There are various types of constraints, and they serve different purposes. The most common types include:
Primary Key Constraint
A primary key uniquely identifies each record in a table.
To add a PRIMARY KEY constraint to a table, you use the ALTER TABLE statement with the ADD keyword and the PRIMARY KEY keyword.
ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY (ColumnName);
Foreign Key Constraint
A foreign key establishes a link between two tables by referencing the primary key of one table as a foreign key in another.
To add a foreign key constraint, you use the ALTER TABLE statement with the ADD keyword and the FOREIGN KEY keyword.
ALTER TABLE ChildTableName ADD CONSTRAINT FK_ChildTableName FOREIGN KEY (ForeignKeyColumn) REFERENCES ParentTableName (PrimaryKeyColumn);
Unique Constraint
A unique constraint ensures that all values in a column (or a set of columns) are unique.
To add a unique constraint, you use the ALTER TABLE statement with the ADD keyword and the UNIQUE keyword.
ALTER TABLE TableName ADD CONSTRAINT UQ_TableName_ColumnName UNIQUE (ColumnName);
Check Constraint
A check constraint enforces a condition that values in a column must satisfy.
To add a check constraint, you use the ALTER TABLE statement with the ADD keyword and the CHECK keyword.
ALTER TABLE TableName ADD CONSTRAINT CK_TableName_ColumnName CHECK (ColumnCondition);
Default Constraint
A default constraint specifies a default value for a column if no value is explicitly provided during an INSERT operation.
To add a default constraint, you use the ALTER TABLE statement with the ADD keyword and the DEFAULT keyword.
ALTER TABLE TableName ADD CONSTRAINT DF_TableName_ColumnName DEFAULT DefaultValue;
Remember to replace “TableName,” “ColumnName,” and other placeholders with your actual table and column names. Adding constraints to tables is an essential part of database design, contributing to the overall data integrity and reliability of the system.