Data Validation and Constraints
Data validation and constraints are essential components of a well-designed database. They ensure the accuracy, integrity, and consistency of the data stored in the database.
- NOT NULL Constraint:
- Ensure that a column cannot contain NULL values.
CREATE TABLE Users (
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL
);
- UNIQUE Constraint:
- Enforce the uniqueness of values in a column.
CREATE TABLE Users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
password VARCHAR(100) NOT NULL
);
- PRIMARY KEY Constraint:
- Define a unique identifier for each row in a table.
CREATE TABLE Users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE,
password VARCHAR(100) NOT NULL
);
- FOREIGN KEY Constraint:
- Establish a relationship between tables based on a column or columns.
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
user_id INT,
order_date DATE,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
- CHECK Constraint:
- Specify a condition that must be true for the data in a column.
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
quantity INT,
price DECIMAL(10, 2),
CHECK (quantity >= 0 AND price > 0)
);
- Data Validation using Constraints:
- Ensure a specific range of values for a column.
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
age INT CHECK (age >= 18 AND age <= 65),
salary DECIMAL(10, 2) CHECK (salary >= 0)
);