Practice SQL Constraints Step by Step

 

 

 SQL Constraints

Constraints are used to control and validated the data in a table.

Types of constraints

• Primary Key
• Unique
• Check
• Not Null
• Default
• Foreign Key
  

 

The Objectives of Table Constraints:

• Ensure data integrity and validity
• Enforce business logic
• Enforce relationship integrity among database tables

 

Primary Key Constraint

Primary key is used to identify that each row is unique and not null.

 

Methods of Adding Constraints

• Row Level Constraints
• Table Level Constraint
  

 

 Creating a table with a primary key on emp_id column using row level constraint

CREATE TABLE employees 

(     

      emp_id INT PRIMARY KEY,

      first_name VARCHAR(20),

      last_name VARCHAR(20),

      age INT NOT NULL,

      email VARCHAR(40) UNIQUE

);

 

Table Level Constraint

 Adding constraint using table level method which is the preferred by most developers.

 

CREATE TABLE employees 

(   

    emp_id INT,

    first_name VARCHAR(20),

    last_name VARCHAR(20),

    age INT,

    email VARCHAR(40),

    CONSTRAINT emp_pk PRIMARY KEY,

 );

Dropping Constraints

ALTER TABLE employees DROP CONSTRAINT PRIMARY KEY;

SQL Unique Constraint 

ALTER TABLE employees

ADD CONSTRAINT emp_email_uq UNIQUE

SQL CHECK Constraint

Create Table with check constraint for age column

CREATE TABLE customers
( 

   customer_id INT,
   customer_name VARCHAR(20),
   age INT CHECK (age BETWEEN 20 AND50)
);

 SQL Default Constraint 

Example: creating customers table with default constraint for salary column.

CREATE TABLE customers
(   

    customer_id INT,
    customer_name VARCHAR(20),
    salary INT DEFAULT 3000,
    age int CHECK (age BETWEEN 20 and 50)
);

SQL Foreign Key Constraint

Foreign key constraint is used to enforce the relationship between data in different tables, 

foreign key column can be a single column or combination of columns. 

 

CREATE TABLE departments
(
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(20)
)

create TABLE Employees

(   

    emp_id int PRIMARY KEY,
    emp_name VARCHAR(20),
    salary NUMERIC(5,2),
    dept_id INT REFERENCES departments(dept_id)
)

Foreign Key

Example: Table level method is used after table creation.

CREATE TABLE departments
(
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(20)
)

create TABLE employees
(

  Emp_id int PRIMARY KEY,
  Emp_name VARCHAR(20),
  Salary NUMERIC(5,2),
  dept_id int
)

ALTER TABLE employees
ADD CONSTRAINT dept_emp_fk
FOREIGN KEY(dept_id)
REFERENCES departments(dept_id)