SQL

🧠 Day 6: SQL Constraints and Table Modification Commands

Photo of Mathimaa Mathimaa3 weeks ago
0 0 2 minutes read

🧠 Day 6: SQL Constraints and Table Modification Commands

⚙️ SQL Command Categories Recap

Let’s quickly recall the main types of SQL commands:

Category Full Form Purpose
DDL Data Definition Language Defines and modifies database structure
DML Data Manipulation Language Handles data (Insert, Update, Delete)
TCL Transaction Control Language Manages database transactions
DQL Data Query Language Retrieves data from tables

🏗️ 1️⃣ DDL Commands – Structure Definition

Command Purpose
CREATE Creates a new table or database
ALTER Adds or modifies columns
RENAME Changes table name
TRUNCATE Deletes all data but keeps structure
DROP Deletes table with structure

🔹 Syntax:

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype,
...
);

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name MODIFY column_name new_datatype;
RENAME old_table_name TO new_table_name;
TRUNCATE TABLE table_name;
DROP TABLE table_name;

✏️ 2️⃣ DML Commands – Data Handling

Command Description
INSERT Adds new records
UPDATE Modifies existing data
DELETE Removes records

🔹 Basic Syntax:

INSERT INTO table_name VALUES (value1, value2, ...);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;

🔹 Insert Multiple Rows at Once:

INSERT INTO table_name (column1, column2, column3)
VALUES
(value1a, value2a, value3a),
(value1b, value2b, value3b),
(value1c, value2c, value3c);

🔹 Insert All Example:

INSERT ALL
INTO table_name VALUES (101, 'Amit', 5000)
INTO table_name VALUES (102, 'John', 7000)
INTO table_name VALUES (103, 'Divya', 8000)
SELECT * FROM dual;

🔒 3️⃣ TCL Commands – Transaction Control

Command Description
COMMIT Saves changes permanently
ROLLBACK Reverts to the previous commit
SAVEPOINT Creates a checkpoint inside a transaction

🔍 4️⃣ DQL Command – Data Query

Used to retrieve data from tables.

SELECT * FROM table_name;
SELECT column1, column2 FROM table_name WHERE condition;

🧩 SQL Constraints – Ensuring Data Integrity

Constraints are rules applied to columns to maintain accuracy and consistency in data.

📘 Types of Constraints

Constraint Purpose Allows NULL? Allows Duplicates?
PRIMARY KEY Ensures unique identity for each record ❌ No ❌ No
FOREIGN KEY Links two tables, maintaining referential integrity ✅ Yes ✅ Yes
UNIQUE Ensures all values in a column are different ✅ Yes ❌ No
NOT NULL Ensures a column cannot store NULL values ❌ No ✅ Yes
CHECK Validates data based on a condition Depends Depends
DEFAULT Assigns a default value if no value is provided ✅ Yes ✅ Yes

🔹 Example: Creating a Table with Constraints

CREATE TABLE Stud (
ID NUMBER PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Salary NUMBER UNIQUE
);

🔹 Insert Data:

INSERT INTO Stud VALUES (100, 'James', 10000);
INSERT INTO Stud VALUES (200, 'John', 20000);
INSERT INTO Stud VALUES (300, 'Saravanan', 30000);
INSERT INTO Stud VALUES (400, 'King', 40000);
INSERT INTO Stud VALUES (500, 'King', 50000);
INSERT INTO Stud VALUES (600, 'Balaji', NULL);

🔹 Query Data:

SELECT * FROM Stud;

🛠️ Modify Existing Table Structure

You can modify column data types or constraints after creation using ALTER TABLE.

🔹 Example:

ALTER TABLE student_details
MODIFY (DOA DATE);

✅ Quick Recap

  • Constraints enforce data rules — like uniqueness, non-null, relationships, and default values.

  • Primary Key: Unique + Not Null

  • Foreign Key: Creates relationships between tables

  • Check: Validates conditions (e.g., CHECK (salary > 0))

  • Alter Table: Used to modify structure

  • Insert All: Helps insert multiple rows efficiently

🧾 Key Takeaway

Using constraints helps ensure data quality and relational consistency in your SQL databases — making your schema strong, error-free, and reliable.

Photo of Mathimaa Mathimaa3 weeks ago
0 0 2 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button