SQL Constraints

Types of SQL Constraints

– NOT NULL

– UNIQUE

– CHECK

– Primary Key

– Foreign Key 

SQL NOT NULL Constraint.

By default, a column can hold NULL value. If we not want to allow NULL or empty value in a column of our table, then we need to place an SQL Constraint on this column specifying that NULL empty column is now not an allowable value. 

Example of NOT NULL Constraint

CREATE TABLE Employees

(empID integer NOT NULL,

FirstName varchar (30) NOT NULL,

LastName varchar(30)); 

Columns “empID” and “FirstName” cannot include NULL, while “LastName” can include NULL, while inserting the record in this table we must provide empID and FirstName after this SQL NOT NULL Constraints apply.

SQL UNIQUE Constraint.

The UNIQUE SQL constraint ensures that all values in a column are distinct and unique values repetition will not allow after applying SQL UNIQUE Constraint. 

Example of SQL UNIQUE Constraint

CREATE TABLE Employees

(empID integer UNIQUE,

FirstName varchar (30),

LastName varchar(30));

Column “empID” cannot include duplicate values, while such constraint does not hold for columns “FirstName” and “LastName” so these last two can hold duplication. 

Please note that a column that is specified as a primary key must also be unique. At the same time, a column that’s unique may or may not be a primary key. 

SQL CHECK Constraint.

The SQL CHECK constraint ensures that all values in a column satisfy certain conditions, suppose we are wishing to enter some conditional record like age of employ not less then 20 so we need to verify it with SQL CHECK Constraint.

Example of SQL CHECK Constraint SQL Primary Key

CREATE TABLE Employees

(empID integer CHECK (empID > 10),

FirstName varchar (30),

LastName varchar(30)); 

Column “empID” must only include integers greater than 10. Less then 10 if we try to enter a record then the constraint will force us to enter grater then 10, so we can’t enter less then 10 in the “empID” column.

Please note that the SQL CHECK constraint does not get enforced by MySQL database at this time.

SQL Primary Key

SQL Tutorial will guide us now, that an SQL Primary Key is used to uniquely identify each row in our table. It can either be part of the actual record itself , or it can be an artificial field one that has nothing to do with the actual record. An SQL Primary Key can consist of one or more fields on a table. When multiple fields are used as an SQL primary key, they are called a composite SQL primary key. 

Primary keys can be specified either when the table is created (using SQL CREATE TABLE) or by changing the existing table structure (using SQL ALTER TABLE). 

Example of SQL PRIMARY Key Constraint with MySQL Create Table.

CREATE TABLE Employees

(empID integer,

FirstName varchar(30),

LastName varchar(30),

PRIMARY KEY (empID)); 

Example of SQL PRIMARY Key Constraints with Oracle Create Table.

CREATE TABLE Employees

(empID integer PRIMARY KEY,

FirstName varchar(30),

LastName varchar(30));

Example of SQL PRIMARY Key Constraints wtih SQL Server Create Table.

CREATE TABLE Employees

(empID integer PRIMARY KEY,

FirstName varchar(30),

LastName varchar(30)); 

Below are examples for specifying an SQL Primary Key Constraint by using SQL ALTER Table Command: 

SQL PRIMARY Key Constraint Example with MySQL Alter Table.

ALTER TABLE Employees ADD PRIMARY KEY (empID); 

SQL PRIMARY Key Constraint Example with Oracle Alter Table.

ALTER TABLE Employees ADD PRIMARY KEY (empID); 

SQL PRIMARY Key Constraint Example with SQL Server Alter Table. 

ALTER TABLE Employees ADD PRIMARY KEY (empID); 

Note: Before using the SQL ALTER TABLE command to add an SQL Primary Key Constraint, we’ll need to make sure that the field is defined as ‘NOT NULL’ in database, other wise NULL cannot be an accepted value for that field we must make that field or column as ‘NOT NULL’

SQL Foreign Key

Now SQL Tutorial will discus SQL Foreign Key Constraint, Foreign Key Constraint is a field or fields that points to the SQL Primary Key Constraint of another table. The purpose of the SQL Foreign Key Constraint is to ensure referential integrity of the data. In other words, only those values that are supposed to appear in the database are permitted.

For example, we have two tables, a Employees table that includes all employees data, and an Employees_Salary table that includes all employees salary records. The constraint here is that all employees salaries must be associated with an employee that is already in the Employees table. In this case, we will place an SQL Foreign Key Constraint on the Employees_Salary table and have it relate to the SQL Primary Key Constraint of the Employees table. Now, we can ensure that all employees salary in the Employees_Salary table are related to a employees in the Employees table. In other words, the Employees_Salary table cannot contain information of any employee that is not in the Employees table. 

Examples of SQL Foreign Key Constraints

The structure of these two tables will be as follows to explain the SQL Foreign Key Constraint 

Structure of Employees

empID Integer, Not Null and Primary Key

FirstName Varchar(50)

LastName Varchar(50)

Email Varchar(50)

Phone Integer

Structure of Employees_Salary

salID Integer, Not Null

Month Date and Time

Salary Integer

empID Foreign Key

In the above structure example, the empID column in the Employees_Salary table is a foreign key pointing to the empID column in the Employees table we can also change its name from empID to some others one but this convention is not recommended. And if we use different names then the database will not generate error or did not disallow us. But for our own acknowledgment and for our own convenience we always use the same names for the Primary key column and Foreign Key columns. 

Primary And Foreign Key Constraint With MySQL Create Table.

CREATE TABLE Employees_Salary

(salID integer,

Month date,

Salary integer,

empID integer,

Primary Key (salID),

Foreign Key (empID) references Employees(empID)); 

Foreign Key Constraint With Oracle Create Table.

CREATE TABLE Employees_Salary

(salID integer primary key,

Month date,

Salary integer,

empID integer references Employees(empID)) ;

Foreign Key Constraint With SQL Server Create Table.

CREATE TABLE Employees_Salary

(salID integer primary key,

Month datetime,

Salary integer,

empID integer references Employees(empID)) ;

Below are examples for specifying an SQL Foreign Key Constraint by SQL Altering Table command. Now assume that the Employees_Salary table has been created, and the foreign key has not yet been put in. 

Foreign Key Constraints With MySQL Alter Table.

ALTER TABLE Employees_Salary

ADD FOREIGN KEY (empID) REFERENCES Employees(empID); 

Foreign Key Constraints With Oracle Alter Table.

ALTER TABLE Employees_Salary

ADD (CONSTRAINTS fk_Foreign_Key_Name) FOREIGN KEY (empID)REFERENCES Employees(empID); 

Foreign Key Constraints With SQL Server Alter Table.

ALTER TABLE Employees_Salary

ADD FOREIGN KEY (empID) REFERENCES Employees(empID);

Comments

comments

Leave a Comment

Time limit is exhausted. Please reload CAPTCHA.