SQL Constraints

SQL constraints are rules applied to table columns to enforce data integrity and consistency. They ensure that the data stored in a database is accurate and reliable. Here are some common SQL constraints with examples:

1. PRIMARY KEY: Ensures that each row in a table is uniquely identifiable. A primary key column cannot have NULL values.

   Example:

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50)
   );
   

   Output: This creates a table where EmployeeID is a primary key, meaning each employee must have a unique ID and cannot be NULL.

2. FOREIGN KEY: Ensures that the value in a column (or a group of columns) matches a value in another table's primary key column. It maintains referential integrity between tables.

   Example:

   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       DepartmentName VARCHAR(50)
   );
   
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );
   

   Output: The Employees table has a DepartmentID column that must match an existing DepartmentID in the Departments table.

3. UNIQUE: Ensures that all values in a column (or a group of columns) are unique across the table. Unlike primary keys, unique columns can accept NULL values, but each non-null value must be unique.

   Example:

   CREATE TABLE Users (
       UserID INT PRIMARY KEY,
       Username VARCHAR(50) UNIQUE,
       Email VARCHAR(100) UNIQUE
   );
   

   Output: Both Username and Email must be unique for every row in the Users table.

4. NOT NULL: Ensures that a column cannot have a NULL value. Every row must have a value for this column.

   Example:

   CREATE TABLE Products (
       ProductID INT PRIMARY KEY,
       ProductName VARCHAR(100) NOT NULL,
       Price DECIMAL(10, 2) NOT NULL
   );
   

   Output: ProductName and Price cannot be NULL in the Products table.

5. CHECK: Ensures that all values in a column satisfy a specific condition.

   Example:

   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       OrderDate DATE,
       TotalAmount DECIMAL(10, 2),
       CHECK (TotalAmount >= 0)
   );
   

   Output: The TotalAmount must be zero or a positive number.

6. DEFAULT: Provides a default value for a column when no value is specified during an INSERT operation.

   Example:

   CREATE TABLE Products (
       ProductID INT PRIMARY KEY,
       ProductName VARCHAR(100),
       Stock INT DEFAULT 0
   );
   

   Output: If no Stock value is provided during an insert, it defaults to 0.

By using these constraints, you can ensure that your data adheres to business rules and maintains its integrity across the database.