Creating Databases, Tables, Constraints, and Keys

1️⃣ How to Create a Database

To create a new database in SQL:

CREATE DATABASE CompanyDB;

To use that database:

USE CompanyDB;

2️⃣ SQL Constraints

Constraints are rules applied to table columns to ensure data integrity.

| Constraint  | Purpose                               | Example                                              |
|------------|---------------------------------------|------------------------------------------------------|
| NOT NULL    | Column must have a value              | Name VARCHAR(50) NOT NULL                            |
| UNIQUE      | Column values must be unique          | Email VARCHAR(100) UNIQUE                            |
| PRIMARY KEY | Uniquely identifies each row          | EmployeeID INT PRIMARY KEY                           |
| FOREIGN KEY | Links column to another table         | DeptID INT FOREIGN KEY REFERENCES Department(DeptID) |
| CHECK       | Ensures value meets a condition       | Salary INT CHECK(Salary>0)                           |
| DEFAULT     | Sets a default value if none provided | JoinDate DATE DEFAULT GETDATE()                      |

3️⃣ Creating Tables

To create a table with constraints:

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    DepartmentID INT,
    Salary INT CHECK(Salary>0),
    JoinDate DATE DEFAULT GETDATE(),
    FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID)
);

• PRIMARY KEY ensures each record is unique
• FOREIGN KEY links Employee.DepartmentID to Department.DeptID
• CHECK ensures salary > 0
• DEFAULT sets a default date

4️⃣ Altering Tables

You can modify existing tables using ALTER TABLE:

Add a Column

ALTER TABLE Employee
ADD Email VARCHAR(100) UNIQUE;

Modify a Column

ALTER TABLE Employee
MODIFY Salary INT NOT NULL;

Drop a Column

ALTER TABLE Employee
DROP COLUMN Email;

5️⃣ Dropping Tables

Remove a table using DROP TABLE:

DROP TABLE Employee;
DROP TABLE Department;

⚠️ Be careful: Dropping a table deletes all data permanently.

6️⃣ Primary Key

• Ensures each row in the table is unique
• Usually an ID column
• Automatically NOT NULL

Example:

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL
);

7️⃣ Foreign Key

• Links a column to a primary key in another table
• Maintains referential integrity

Example:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID)
);

• Cannot insert an Employee with a DepartmentID that doesn’t exist in the Department table

8️⃣ Beginner Example: Complete Setup

-- Create database
CREATE DATABASE CompanyDB;
USE CompanyDB;

-- Create Department Table
CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL UNIQUE
);

-- Create Employee Table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    DepartmentID INT,
    Salary INT CHECK(Salary>0),
    JoinDate DATE DEFAULT GETDATE(),
    FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID)
);