Types of CONSTRAINTS in SQL
The SQL Constraints are an integrity which defines some conditions that restrict the column to stay true while inserting or updating or deleting data within the column. Constraints are often specified when the table created first with CREATE TABLE statement or at the time of modification of the structure of an existing table with ALTER TABLE statement.
The SQL Constraints are used to implement the rules of the table. If there's any violation of the constraints caused some action not performing properly on the table the action is aborted by the constraint. Some Constraints are often used along side the SQL CREATE TABLE statement.
The general structure of the SQL Constraint is defined as:
The CONSTRAINT keyword is followed by a constraint name followed by a column or an inventory of columns.
Constraints are wont to specify rules for the info during a table. Constraints are wont to limit the sort of knowledge which will enter a table. This ensures the accuracy and reliability of the info within the table. If there's any violation between the constraint and therefore the data action, the action is aborted.
Integrity constraint violations. Integrity constraint violations occur when an insert, update, or delete statement violates a primary key, foreign key, check, or unique constraint or a singular index.
NOT NULL constraint
The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a worth , which suggests that you simply cannot insert a replacement record, or update a record without adding a worth to the present field.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
Unique constraint
A unique constraint may be a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long because the combination of values is exclusive .
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
PRIMARY KEY constraint
The PRIMARY KEY constraint uniquely identifies each record during a table. Primary keys must contain UNIQUE values, and can't contain NULL values. A table can have just one primary key; and within the table, this primary key can contains single or multiple columns (fields)
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Difference Between Unique constraint And PRIMARY KEY constraint
. Parameter Primary key Unique key
Basic | Used to serve as a unique identifier for each row in a table. | Uniquely determines a row which isn’t primary key. |
NULL value acceptance | Cannot accept NULL values. | Can accept one NULL value. |
Number of keys that can be defined in the table | Only one primary key | More than one unique key |
Index | Creates clustered index | Creates non-clustered index |
Check constraint
A check constraint may be a sort of integrity constraint in SQL which specifies a requirement that has got to be met by each row during a database table. The constraint must be a predicate. It can ask one column, or multiple columns of the table.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
FOREIGN KEY constraint
A FOREIGN key's a key wont to link two tables together.A FOREIGN key's a field (or collection of fields) in one table that refers to the first KEY in another table.The table containing the foreign key's called the kid table, and therefore the table containing the candidate key's called the referenced or parent table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
informational constraint
An informational constraint may be a constraint attribute which will be employed by the SQL compiler to enhance the access to data. Informational constraints aren't enforced by the database manager, and aren't used for extra verification of data; rather, they're wont to improve query performance.