SQL Constraint | Types of SQL Constraint

SQL Constraint is the most important key factor for any table or schema designing. Keeping this in mind the interviewer can ask different questions on Constraints. In this article, we will walk through SQL Constraints and Types of Constraints.

Please visit our YouTube Channel for SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Introduction

In the database, a constraint consists of one or more columns of a table that have specific properties. So when you define a table, you specify the constraints. There are three keys also which is known as Primary Key Constraint, Unique Key Constraint, and Foreign Key Constraint.

SQL Constraints

In SQL, Constraints are the rules that the database enforces to improve data integrity. They are also used to specify the business rules for data in a table.

SQL Constraints are used to restrict the type of data that can be inserted into a table. By doing so, it ensures the accuracy and reliability of the data in the table.

If there is any violation between the defined constraint and the data to be inserted, updated, or deleted, the respective action is aborted or terminated by the SQL.

A user can define constraints either at column level or table level. The column-level constraints apply only to a column, while table-level constraints apply to the whole table or set of columns.

SQL Constraints can be specified when the table is created by using CREATE TABLE statement, or after the table is created by using ALTER TABLE statement.

Types of SQL Constraints

There are the following Constraints in SQL:

NOT NULL Constraint
UNIQUE Constraint or UNIQUE Key Constraint
PRIMARY Key Constraint
FOREIGN Key Constraint
CHECK Constraint
DEFAULT Constraint

1. NOT NULL CONSTRAINT

This constraint ensures that a column cannot have a NULL value. By default all the columns are nullable. There can be multiple NOT NULL Constraint in any table.

The SQL NOT NULL Constraint can be used with other constraints together as well. Primary Key Constraint uses it along with Unique Key internally. We also use the NOT NULL constraint along with UNIQUE Key Constraint to avoid NULL values.

Please watch the SQL NULL values video here and the SQL Three Valued Logic video here for more details.

2. UNIQUE KEY CONSTRAINT

The UNIQUE Constraint ensures that all values in a column or set of columns are unique. This constraint prevents duplicate values in any table. There can be multiple UNIQUE KEY Constraints in any table. A UNIQUE KEY can also be a composite key.

The UNIQUE Key constraint allows the NULL value by default (only one NULL value). So you can prevent the NULL value by combining the NOT NULL Constraint in the UNIQUE KEY Constraint.

UNIQUE KEY can be created on Temp Table as well. Not only the temp tables but Table Variable and Table-Valued Type can also contain Unique Key Constraint.

Please read more about Unique Key Constraint here and the Unique Key Interview Questions and Answers article here for more details.

Please watch the UNIQUE KEY Constraint video here for more details.

3. PRIMARY KEY CONSTRAINT

The PRIMARY KEY Constraint is used to uniquely identify each individual record of the table. It can be either any static column or an Identity Column. The purpose of the PRIMARY KEY is to enforce Entity Integrity in the column.

A PRIMARY KEY is a value that is unique for every record in the table and a record can be accessed by the Primary Key value directly without affecting any other record of the table.

A PRIMARY KEY can also be created on Temp Table. Not only the temp table but Table Variables and Table-Valued Type can also contain Primary Key constraints.

Please read more about Primary Key Constraint here and the Primary Key Interview Questions and Answers article here for more details.

Please watch the Primary Key Constraint video here for more details.

4. FOREIGN KEY CONSTRAINT

A FOREIGN KEY Constraint is basically used to create a relationship between two tables. This constraint prevents actions that would destroy relational links between the tables.

A FOREIGN KEY is a constraint that enforces Referential Integrity in the SQL Server Database. They always enforce and ensure that the referential integrity is preserved.

A FOREIGN KEY can be a set of one or more than one column as well. In that case, it is known as a Composite Foreign Key.

A Foreign Key Constraint prevents you from inserting or updating values that aren’t found in the related reference table's column. It also prevents deleting the records from the parent table.

A table can contain multiple Foreign Keys. In fact, a single column can have multiple Foreign Key Constraints.

A Foreign Key cannot be created on Temp Table. Not only the Temp Table but Table Variables and Table-Valued Type also cannot contain any Foreign Key Constraint.

Please read more about Foreign Key Constraint here and Foreign Key Interview Questions and Answers article here for more details.

Please watch the Foreign Key video here for more details.

5. SQL CHECK CONSTRAINT

The CHECK Constraints are nothing but the rules or set of rules that help to validate the value being inserted or updated) into a column of table based on a certain condition. They ensure that all the data in a column contains validated values according to the check constraint rule.

If the check condition evaluates to false, the record violates the constraint and isn't inserted into the table. It can be defined while creating the table or by ALTER command if the table is already created.

Check Constraints cannot be defined on Views.
The definition of a Check Constraint cannot include a subquery. Please watch the subquery video here for more details.
The Check Constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
The NULL values are often evaluated as true by check constraints. So using the NOT NULL constraints is the best method to control a field contains a NULL value or not.
A Check Constraint can also use a scalar-valued user-defined function for evaluation logic.
You can also disable the Check Constraint. But doing so turns the table column into a 'NonTrusted' state.

6. SQL DEFAULT Constraint

In SQL, a DEFAULT Constraint provides the default value when no value is supplied into the column. Actually, there should be some value in all the columns. So this DEFAULT constraint is used to specify a default value explicitly if none is supplied.

The ANSI SQL standard defines DEFAULT as being a column attribute whereas Microsoft implemented DEFAULT as a type of constraint. So you cannot change a DEFAULT using ALTER TABLE...ALTER COLUMN syntax. Therefore, you have to drop the DEFAULT Constraint and re-add the DEFAULT constraint with the new default value.

Example

A table can contain all the constraints in it and multiple constraints are also allowed except the Primary Key Constraint.

CREATE TABLE tblSQLConstraintsDemo

(

Id INT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY CLUSTERED,

Name VARCHAR(255) NOT NULL,

Email VARCHAR(255) CONSTRAINT UK_Email UNIQUE NONCLUSTERED,

Age INT CHECK(Age > 18),

ManagerID INT CONSTRAINT FK_ManagerID FOREIGN KEY

REFERENCES tblSQLConstraintsDemo(Id),

CreatedOn DATE DEFAULT GETDATE()

)

We can also add a constraint later by ALTER Command as well:

ALTER TABLE tblSQLConstraintsDemo ALTER COLUMN Email VARCHAR(255) NOT NULL;

ALTER TABLE tblSQLConstraintsDemo ADD CONSTRAINT UK_IdEmail UNIQUE (Id, Email);

We can delete the constraints by DROP Command:

ALTER TABLE tblSQLConstraintsDemo DROP CONSTRAINT UK_IdEmail;

SQL Primary Key Constraint

SQL Unique Key Constraint

SQL Foreign Key Constraint

SQL Primary Key Interview Questions and Answers

SQL Unique Key Interview Questions and Answers

SQL Foreign Key Interview Questions and Answers

Most Frequently Asked SQL Interview Questions
Most Frequently Asked C# Interview Questions
Foreign Key Interview Questions
Unique Key Interview Questions
Primary Key Interview Questions
Derived Table (DT) Interview Questions
Unicode vs Non-Unicode Data Types Interview Questions
Table Variable (TV) Interview Questions
Table-Valued Type (TVT) Interview Questions
Common Table Expression(CTE) Interview Questions
Temp Table Interview Questions
Static Class Interview Questions and Answers
Sealed Class Interview Questions and Answers
Sealed Method Interview Questions and Answers
Abstract Class vs Interface
Interface Interview Questions and Answers

Thanks for visiting this page. Please follow us on Twitter, Facebook, LinkedIn, Telegram, Youtube, and Quora for regular updates.

Sunday, May 15, 2022

SQL Constraint | Types of SQL Constraint