Check Constraint in MS SQL Server

Adding multiple constraints in a single table

Last Updated : 17 Dec, 2020

Prerequisite - SQL Constraints We can create a table with more than one constraint in its columns. Following example shows how we can define different constraints on a table. Adding constraints in Create command :

Sr_no is a Primary Key.
Branch_no is the foreign key referencing Branch table.
Company type will hold either of the values : ‘1C’, ’2C’, ’3C’

Syntax :

Create table Fd_master(Sr_no varchar2(10), 
Branch_no varchar2(10), account_no varchar2(20),
company_type varchar2(20), constraint pk primary key(Sr_no),  
constraint fk foreign key (Branch_no) references Branch,
constraint chk check(company_type in (‘1C’, ’2C’, ’3C’)));

Primary Key constraint - Sr_no in Fd_master where pk is userdefined name given to Primary key.
Foreign Key constraint - Branch_no in Fd_master where fk is name of foreign key that references branch table.
Check constraint - company_type in Fd_master where chk is name that will check the given values i.e ‘1C’, ‘2C’, ‘3C’

Adding constraints in Alter command : Multiple columns level constraints can be added via alter command. It can be added in parent-child table in a serial order. Constraint 1 (Default) : Create two tables -

Parent having default constraint on ID with the default value 1. Name given to constraint is DF_ParentTable.
Foreign table with ID column.

ID in both the table is made 'NOT NULL'. Syntax :

CREATE TABLE ParentTable (ID int not null constraint DF_ParentTable default (1), 
name varchar2(5));

CREATE TABLE ForeignTable (ID int not null, Col2 VARCHAR(5));

Constraint 2 (Check) :

ALTER TABLE ParentTable ADD CONSTRAINT CK_ParentTable_ID CHECK(ID<100);

Constraint 3 (Foreign Key) : Foreign Key

ALTER TABLE ParentTable 
ADD CONSTRAINT FK_parent_foreign FOREIGN KEY (ID) REFERENCES ForeignTable(ID);

Check Constraint in MS SQL Server

D

dhatriganda07

Improve

Article Tags :

SQL
DBMS-SQL

Similar Reads

SQL | Checking Existing Constraints on a Table using Data Dictionaries

Prerequisite: SQL-Constraints In SQL Server the data dictionary is a set of database tables used to store information about a database's definition. One can use these data dictionaries to check the constraints on an already existing table and to change them(if possible). USER_CONSTRAINTS Data Dictio

SQL Query to Display All the Existing Constraints on a Table

In SQL, we sometimes need to display all the currently existing constraints on a table. The whole process for doing the same is demonstrated below. For this article, we will be using the Microsoft SQL Server as our database. Step 1: Create a Database. For this use the below command to create a datab

How to Create a Table With Multiple Foreign Keys in SQL?

When a non-prime attribute column in one table references the primary key and has the same column as the column of the table which is prime attribute is called a foreign key. It lays the relation between the two tables which majorly helps in the normalization of the tables. A table can have multiple

SQL NOT NULL Constraint

In SQL, constraints are used to enforce rules on data, ensuring the accuracy, consistency, and integrity of the data stored in a database. One of the most commonly used constraints is the NOT NULL constraint, which ensures that a column cannot have NULL values. This is important for maintaining data

Check Constraint in MS SQL Server

Check Constraint : It is used alongside relational operators to check whether a value satisfies the condition or not (boolean). If the condition is satisfied, the boolean expression sets to True otherwise False. The check constraint does not have a specific syntax. It is used along with the create t

SQL Query to Add Unique key Constraints Using ALTER Command

Here we will see how to add unique key constraint to a column(s) of a MS SQL Server's database with the help of a SQL query using ALTER clause. For the demonstration purpose, we will be creating a demo table in a database called "geeks". Creating the Database : Use the below SQL statement to create

SQL Query to Demonstrate Addition Anomaly in Referential Integrity in a Table

In SQL, there exists a concept of referential integrity. This means that a foreign key can take reference from the primary key of another table. There exists basically 3 anomalies in this concept. Here, we discuss about Addition/Insertion Anomaly. This means that if an entry is absent in the primary

SQL Query to Add Foreign Key Constraints Using ALTER Command

In relational databases, a Foreign Key Constraint is a fundamental concept that defines a relationship between two tables. It ensures that the value of one or more columns in a child table corresponds to the value in a primary key or unique key in a parent table.In this article, we will look into ho

Create a Table if it Doesn't Exist in SQL

Creating tables is a fundamental part of database management and sometimes it is important to ensure a table doesnâ€™t already exist before creating it. The CREATE TABLE IF NOT EXISTS statement in SQL provides a simple and effective way to handle such situations prevent errors and simplify database ma

Unique Constraint in MS SQL Server

A table might have repetitive data in form of rows. This might cause glitches while retrieving data from the query. To avoid them, a unique constraint is used. Unique allows sorting a column or set of columns uniquely meaning, a user cannot insert a duplicate or repeated value in a column as it resu