SQL Constraints and Database Commands (CBSE Class 12 Computer Science - 083)

When designing a database, it is important to ensure that only valid and meaningful data is stored. SQL provides Constraints to enforce rules on table columns. SQL also provides commands to create, select, display, and delete databases.

Learning Objectives

• Understand SQL Constraints.
• Learn NOT NULL, UNIQUE, and PRIMARY KEY constraints.
• Create and manage databases.
• Understand database-level SQL commands.

---

What are SQL Constraints?

Constraints are rules applied to table columns that restrict the type of data that can be inserted. They help maintain the accuracy, consistency, and integrity of data.

---

Why are Constraints Required?

• Maintain data accuracy.
• Prevent invalid data entry.
• Avoid duplicate records.
• Ensure data consistency.
• Maintain relationships between tables.

---

Types of Constraints (CBSE Syllabus)

Constraint	Purpose
NOT NULL	Prevents NULL values.
UNIQUE	Prevents duplicate values.
PRIMARY KEY	Uniquely identifies each record.

---

1. NOT NULL Constraint

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

Syntax

ColumnName DataType NOT NULL

Example

CREATE TABLE Student
(
    StudentID INT,
    Name VARCHAR(30) NOT NULL,
    Marks INT
);

In the above table, the Name column must always contain a value.

---

2. UNIQUE Constraint

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

Syntax

ColumnName DataType UNIQUE

Example

CREATE TABLE Student
(
    AdmissionNo INT UNIQUE,
    Name VARCHAR(30),
    Marks INT
);

No two students can have the same Admission Number.

---

3. PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies every record in a table.

A Primary Key:

• Must contain unique values.
• Cannot contain NULL values.
• There can be only one Primary Key in a table.

Syntax

ColumnName DataType PRIMARY KEY

Example

CREATE TABLE Student
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(30),
    Marks INT
);

---

Difference Between UNIQUE and PRIMARY KEY

UNIQUE	PRIMARY KEY
Prevents duplicate values.	Uniquely identifies each record.
Can contain one NULL value (implementation-dependent; in MySQL multiple NULLs are allowed).	Cannot contain NULL values.
Multiple UNIQUE constraints can exist.	Only one PRIMARY KEY is allowed.

---

Database Commands

Before creating tables, a database must be created and selected. SQL provides commands for managing databases.

---

CREATE DATABASE

The CREATE DATABASE command creates a new database.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE School;

---

USE DATABASE

The USE command selects a database so that SQL commands are executed within it.

Syntax

USE database_name;

Example

USE School;

---

SHOW DATABASES

The SHOW DATABASES command displays all databases available in the database server.

Syntax

SHOW DATABASES;

Example Output

+------------------+
| Database         |
+------------------+
| mysql            |
| school           |
| information_schema|
+------------------+

---

DROP DATABASE

The DROP DATABASE command permanently deletes a database and all the tables stored inside it.

Syntax

DROP DATABASE database_name;

Example

DROP DATABASE School;

---

Complete Example

CREATE DATABASE School;

USE School;

CREATE TABLE Student
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    AdmissionNo INT UNIQUE,
    Marks INT
);

---

Execution Sequence

CREATE DATABASE
        │
        ▼
USE DATABASE
        │
        ▼
CREATE TABLE
        │
        ▼
INSERT DATA

---

Summary of Database Commands

Command	Purpose
CREATE DATABASE	Creates a new database.
USE	Selects a database.
SHOW DATABASES	Displays all databases.
DROP DATABASE	Deletes an entire database.

---

Summary of Constraints

Constraint	Purpose
NOT NULL	Prevents empty values.
UNIQUE	Allows only unique values.
PRIMARY KEY	Uniquely identifies every record.

---

Common Errors

• Using duplicate values in a PRIMARY KEY column.
• Leaving a NOT NULL field empty.
• Creating a table without selecting a database using the USE command.
• Dropping a database accidentally.
• Assuming UNIQUE and PRIMARY KEY are exactly the same.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. What is a constraint in SQL?

A constraint is a rule applied to table columns to ensure that only valid data is stored.

2. What is the purpose of the NOT NULL constraint?

It ensures that a column cannot contain NULL (empty) values.

3. What is the difference between UNIQUE and PRIMARY KEY?

Both prevent duplicate values, but a PRIMARY KEY also does not allow NULL values and uniquely identifies each record in a table.

4. Which SQL command is used to create a database?

The CREATE DATABASE command is used to create a new database.

5. Which command displays all databases?

The SHOW DATABASES command displays all databases available on the database server.

---

Summary

• Constraints maintain the accuracy and integrity of data.
• NOT NULL prevents empty values.
• UNIQUE prevents duplicate values.
• PRIMARY KEY uniquely identifies each record and cannot contain NULL values.
• CREATE DATABASE creates a new database.
• USE selects the database for further operations.
• SHOW DATABASES displays all databases.
• DROP DATABASE permanently deletes a database and all its contents.