14. Constraints (Rules)

Constraints in SQL Server are rules applied on table columns to ensure data accuracy, consistency, and integrity. They help prevent invalid data from being entered into the database.

1. What are Constraints?

• Rules applied on columns
• Ensure valid and consistent data
• Improve data quality
• Used in real-world applications like banking, e-commerce, etc.

2. Types of Constraints

• Primary Key
• Unique Key
• Foreign Key
• CHECK
• DEFAULT
• NOT NULL

3. Primary Key

What: Uniquely identifies each record.

• No duplicate values
• No NULL values
• Only one per table

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)
);

4. Unique Key

What: Ensures all values are unique.

• No duplicate values
• Allows one NULL value
• Can have multiple unique keys

Email VARCHAR(100) UNIQUE

5. Foreign Key

What: Creates relationship between tables.

• Links two tables
• Maintains referential integrity
• Value must exist in parent table

CREATE TABLE Orders (
    OrderID INT,
    StudentID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(ID)
);

6. CHECK Constraint

What: Validates data based on condition.

• Restricts values
• Ensures valid data

Age INT CHECK (Age >= 18)

7. DEFAULT Constraint

What: Assigns default value if none is provided.

• Automatic value insertion
• Saves time and avoids NULL

City VARCHAR(50) DEFAULT 'Delhi'

8. NOT NULL Constraint

What: Ensures column cannot have NULL values.

• Mandatory field
• Prevents empty values

Name VARCHAR(50) NOT NULL

9. Combined Example

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18),
    City VARCHAR(50) DEFAULT 'Delhi'
);

10. Key Differences

Constraint	Purpose
Primary Key	Unique + Not Null
Unique	Unique values
Foreign Key	Table relationship
CHECK	Condition validation
DEFAULT	Default value
NOT NULL	Mandatory field

Conclusion

Constraints are essential for maintaining data integrity and ensuring correct data entry. They are widely used in real-world database systems.

Your learning journey continues 🚀