Ensure Data Integrity with Primary, Foreign Keys, and More - Textnotes

Ensure Data Integrity with Primary, Foreign Keys, and More

Master SQL data types and constraints in this beginner-friendly guide. Learn how to use PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, default values, and auto-increment columns. Practice adding constraints to employee tables to ensure data integrity.

1. Introduction

Data types and constraints are essential for maintaining data integrity and accuracy in SQL databases.

  1. Data types define the kind of data a column can store (e.g., integer, text, date).
  2. Constraints enforce rules on data to prevent invalid entries.

Key Points:

  1. Constraints help avoid duplicate, null, or inconsistent data.
  2. Default values and auto-increment columns simplify data entry and unique identifiers.

2. SQL Data Types

Data TypeDescription
INT / BIGINTInteger numbers
DECIMAL / NUMERICFixed-point numbers
FLOAT / REALApproximate numbers
VARCHAR(n)Variable-length string
CHAR(n)Fixed-length string
DATE / DATETIMEDate and time
BOOLEANTRUE/FALSE values

3. SQL Constraints

3.1 PRIMARY KEY

  1. Uniquely identifies each row in a table.
  2. Cannot be NULL.

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

3.2 FOREIGN KEY

  1. Maintains referential integrity between two tables.

CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

ALTER TABLE Employees
ADD DeptID INT,
ADD CONSTRAINT FK_Dept FOREIGN KEY (DeptID)
REFERENCES Departments(DeptID);

3.3 UNIQUE

  1. Ensures column values are unique.

ALTER TABLE Employees
ADD CONSTRAINT UQ_Email UNIQUE (Email);

3.4 NOT NULL

  1. Ensures column cannot have NULL values.

ALTER TABLE Employees
MODIFY COLUMN Name VARCHAR(50) NOT NULL;

3.5 CHECK

  1. Ensures column values meet a specific condition.

ALTER TABLE Employees
ADD CONSTRAINT CHK_Salary CHECK (Salary > 0);

4. Default Values and Auto-Increment

4.1 Default Values


ALTER TABLE Employees
ADD CONSTRAINT DF_Country DEFAULT 'India' FOR Country;

4.2 Identity / Auto-Increment Columns

  1. Automatically generate unique numbers for each row.

CREATE TABLE Employees (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10,2)
);

5. Practical Exercises

  1. Add a PRIMARY KEY to the Employees table.
  2. Create a FOREIGN KEY to link Employees to Departments.
  3. Add a UNIQUE constraint on the Email column.
  4. Apply NOT NULL to Name and Department columns.
  5. Add a CHECK constraint to ensure Salary > 0.
  6. Set a default value for the Country column.
  7. Convert ID column to auto-increment for automatic numbering.

6. Tips for Beginners

  1. Always define a PRIMARY KEY for each table.
  2. Use FOREIGN KEYS to maintain relational integrity.
  3. Apply UNIQUE and NOT NULL to prevent bad data.
  4. Use CHECK constraints for business rules like valid salaries or ages.
  5. Use default values and auto-increment to simplify inserts.
  6. Plan constraints before populating large tables for efficiency.


Next Step: After mastering data types and constraints, the next module is Advanced Data Handling in SQL, where you’ll learn temporary tables, table variables, bulk insert, and data import/export.