Sql Data Types And Constraints Complete Guide

Contents

1. Introduction to SQL Data Types

   • Numeric Types
   • Date and Time Types
   • Character Types
   • Large Object Types
   • Boolean Type

2. Constraints in SQL

   • PRIMARY KEY
   • FOREIGN KEY
   • UNIQUE
   • NOT NULL
   • CHECK
   • DEFAULT
   • AUTO_INCREMENT

3. Examples

1. Introduction to SQL Data Types

Data types specify the kind of data that can be stored in a database table's column. They also determine how the data is processed by the SQL engine.

Numeric Types

• INT: Integer values.
• SMALLINT: Small integer values.
• BIGINT: Large integer values.
• FLOAT: Single-precision floating point numbers.
• DOUBLE: Double-precision floating point numbers.
• DECIMAL(precision, scale): Fixed-point numbers where p is the total number of digits and s is the number after the decimal point.

Date and Time Types

• DATE: Year, month, day.
• TIME: Hour, minute, second.
• DATETIME: Date and time together.
• TIMESTAMP: Similar to DATETIME but includes timezone information.
• YEAR: Year value (can be used for birth years, etc.).

Character Types

• CHAR(size): Fixed-size string with length size.
• VARCHAR(size): Variable-size string up to length size.
• TEXT: Large text strings.

Large Object Types

• BLOB: Binary large object (for storing images, videos, etc.).
• CLOB: Character large object (for very large text fields).

Boolean Type

• BOOLEAN: True or False values (often represented as TINYINT(1) in MySQL).

2. Constraints in SQL

Constraints are rules enforced on data columns to limit the type of data that the column can hold.

PRIMARY KEY

Uniquely identifies each record in a table.

FOREIGN KEY

Establishes a link between the data in two tables.

UNIQUE

Ensures that all values in a column are different.

NOT NULL

Indicates that the column cannot have a NULL value.

CHECK

Ensures that all values in a column satisfy certain conditions.

DEFAULT

Provides a default value for a column when no value is specified.

AUTO_INCREMENT

Automatically generates a unique numeric value each time a new record is added.

3. Examples

Let's walk through an example by creating a few related tables with different data types and constraints.

Example: Creating a Simple Database Schema

3.1. Create departments Table

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(255)
);

Explanation:

• department_id: A numeric primary key that automatically increments.
• department_name: A not-null and unique string up to 100 characters.
• location: An optional string (can be NULL.

Inserting Data into departments Table:

INSERT INTO departments (department_name, location) VALUES
('Human Resources', 'Building 1A'),
('Finance', 'Building 1B'),
('IT', 'Building 2');

3.2. Create employees Table

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone_number VARCHAR(15),
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary DECIMAL(8, 2) CHECK (salary > 0),
    manager_id INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Explanation:

• employee_id: Numeric primary key.
• first_name and last_name: Mandatory strings.
• email: Mandatory and unique string.
• phone_number: Optional string.
• hire_date: Mandatory date.
• job_id: Mandatory string.
• salary: Mandatory decimal value greater than zero.
• manager_id: Optionally references another employee in the same table.
• department_id: References the department_id in the departments table.

Inserting Data into employees Table:

INSERT INTO employees (first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES
('John', 'Doe', 'johndoe@example.com', '123-456-7890', '2022-01-15', 'HR_REP', 50000.00, 1),
('Jane', 'Smith', 'janesmith@example.com', '098-765-4321', '2023-03-20', 'ACCT_REP', 60000.50, 2),
('Alice', 'Johnson', 'alicejohnson@example.com', '234-567-8901', '2023-03-10', 'DEV', 75000.75, 3);

3.3. Attempting Invalid Inserts

Let's try inserting invalid data and see what happens.

Invalid Insert: Duplicate department_name

INSERT INTO departments (department_name, location) VALUES ('Human Resources', 'Building 3A');

Result: Error: Duplicate entry for key 'uniq_department_name'

Invalid Insert: email must be unique

INSERT INTO employees (first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) 
VALUES ('Michael', 'Brown', 'johndoe@example.com', '123-456-7890', '2023-04-10', 'HR_REP', 52000.00, 1);

Result: Error: Duplicate entry for key 'uniq_email'

Invalid Insert: salary must be greater than 0

INSERT INTO employees (first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) 
VALUES ('Chris', 'Lee', 'chrislee@example.com', '111-222-3333', '2023-05-01', 'HR_REP', -5000.00, 1);

Result: Error: CHECK constraint failed

3.4. Update and Delete Operations

Updating Data:

UPDATE employees
SET salary = 53000.00
WHERE email = 'johndoe@example.com';

Deleting Data:

Before deleting, let’s ensure the foreign key constraint works correctly.

DELETE FROM departments WHERE department_id = 1;

Result: Error: Cannot delete row because it is referenced by a foreign key constraint from the employees table.

This ensures referential integrity; we need to delete employees from the employees table first.

DELETE FROM employees
WHERE department_id = 1;

DELETE FROM departments
WHERE department_id = 1;

Adding Default Constraint:

Modifying the departments table to add a default location.

ALTER TABLE departments
MODIFY COLUMN location VARCHAR(255) DEFAULT 'Main Building';

Inserting Without Specifying Location:

INSERT INTO departments (department_name) VALUES ('Marketing');

SELECT * FROM departments WHERE department_id = 4;

Result: The location field will be automatically set to 'Main Building'.

4. Adding More Constraints

Let’s enhance our employees table further.

3.5. Create jobs Table

CREATE TABLE jobs (
    job_id VARCHAR(10) PRIMARY KEY,
    job_title VARCHAR(100) NOT NULL UNIQUE,
    min_salary DECIMAL(8, 2) CHECK (min_salary >= 0),
    max_salary DECIMAL(8, 2) CHECK (max_salary >= 0 AND max_salary > min_salary)
);

Explanation:

• job_id: Primary key for each job.
• job_title: Must be unique and non-null.
• min_salary and max_salary: Must be positive, and max_salary should be greater than min_salary.

Inserting Data:

INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
VALUES
('HR_REP', 'Human Resources Representative', 40000.00, 60000.00),
('ACCT_REP', 'Accountant', 50000.00, 80000.00),
('DEV', 'Software Developer', 60000.00, 120000.00);

3.6. Modifying employees Table to Add job_id Constraint

We will use the job_id field already present in the employees table, along with the jobs table.

ALTER TABLE employees
ADD CONSTRAINT fk_job_id FOREIGN KEY (job_id) REFERENCES jobs(job_id);

Now, job_id in the employees table must be present in the jobs table.

-- Valid insert using existing job_id
INSERT INTO employees (first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) 
VALUES ('Chris', 'Lee', 'chrislee@example.com', '123-456-7890', '2023-05-10', 'DEV', 90000.00, 3);

-- Invalid insert using non-existing job_id
INSERT INTO employees (first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) 
VALUES ('Anna', 'Miller', 'annamiller@example.com', '222-333-4444', '2023-06-01', 'INVALID_JOB_ID', 80000.00, 3);

Result: Error: Foreign key constraint failed

Summary

In this guide, we covered:

• Common SQL data types such as INT, VARCHAR, DATE, DECIMAL, etc.
• Basic constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT, and AUTO_INCREMENT.
• Practical examples demonstrating how these types and constraints work in real databases.