SQL Constraints

What are SQL Constraints?

SQL Constraints are rules applied to table columns to ensure that the data stored in the database is accurate, valid, and consistent.

Why are Constraints Used?

• Ensure data accuracy

• Prevent invalid data

• Avoid duplicate records

• Maintain relationships between tables

• Improve data integrity

Types of SQL Constraints

• PRIMARY KEY

• FOREIGN KEY

• NOT NULL

• UNIQUE

• CHECK

• DEFAULT

PRIMARY KEY

• A PRIMARY KEY uniquely identifies each record in a table.

• Values must be unique

• Cannot contain NULL values

• A table can have only one primary key
  

CREATE TABLE Student(
       Student_ID INT PRIMARY KEY,
       Name VARCHAR(50),
       Marks INT
);

FOREIGN KEY

• A FOREIGN KEY creates a relationship between two tables by referring to the primary key of another table.

Department Table

CREATE TABLE Department(
      Dept_ID INT PRIMARY KEY,
      Department_Name VARCHAR(50)
);

Student Table

CREATE TABLE Student(
      Student_ID INT PRIMARY KEY,
      Name VARCHAR(50),
      Dept_ID INT,
      FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);

NOT NULL

• The NOT NULL constraint ensures that a column cannot contain NULL(empty) values.

CREATE TABLE Student(
      Student_ID INT,
      Name VARCHAR(50) NOT NULL
);

UNIQUE

• The UNIQUE constraint ensures that all values in a column are different.

CREATE TABLE Student(
      Student_ID INT PRIMARY KEY,
      Email VARCHAR(100) UNIQUE
);

CHECK

• The CHECK constraint ensures that values meet a specified condition.

CREATE TABLE Student(
      Student_ID INT,
      Marks INT CHECK (Marks >= 0 AND Marks <= 100)
);

DEFAULT

• The DEFAULT constraint assigns a default value if no value is provided.

CREATE TABLE Student(
       Student_ID INT,
       City VARCHAR(50) DEFAULT 'Chennai'
);