Create Tables

• Tables store data in a database and are uniquely named within a schema. Each table contains one or more columns, with each column having a defined data type (e.g., numbers, strings, or temporal data).

• Syntax for creating a table:

CREATE TABLE [database_name.][schema_name.]table_name (
	pk_column data_type PRIMARY KEY,
	column_1 data_type NOT NULL,
	column_2 data_type,
	...,
	table_constraints
);

• Steps:
  1. Database & Schema: Specify the database (optional, defaults to the current) and schema.
  2. Table Name: Define a unique name for the table.
  3. Primary Key: Include a primary key, either single-column (PRIMARY KEY) or multi-column (as a table constraint).
  4. Columns: Define column names, data types, and constraints (e.g., NOT NULL, UNIQUE).
  5. Table Constraints: Add constraints like FOREIGN KEY, CHECK, and UNIQUE.

Example:

CREATE TABLE sales.visits (
	visit_id INT PRIMARY KEY IDENTITY (1, 1),
	first_name VARCHAR (50) NOT NULL,
	last_name VARCHAR (50) NOT NULL,
	visited_at DATETIME,
	phone VARCHAR(20),
	store_id INT NOT NULL,
	FOREIGN KEY (store_id) REFERENCES sales.stores (store_id)
);

• Key Points:
  • visit_id: Primary key with auto-increment (IDENTITY(1,1)).
  • first_name, last_name: VARCHAR columns for strings up to 50 characters.
  • visited_at: Records visit date and time (DATETIME).
  • phone: Optional column (NULL allowed).
  • store_id: Links to the sales.stores table via a FOREIGN KEY constraint.
  • If no database is specified, the table defaults to the current database.

متنساش تتأكد انك عامل USE للـ Database اللي هتشتغل عليها أو مختارها من فوق

USE Database_name;

Constrains

We can make Id increases with 1 or more

visit_id INT PRIMARY KEY IDENTITY (1, 1)

Constraints

SQL Constraints Overview

Constraints in SQL define rules for data stored in a database, ensuring accuracy and integrity. These constraints control what values are allowed in specific columns.

Constraint	Description
NOT NULL	Prevents NULL values in a column
UNIQUE	Ensures unique values in a column
PRIMARY KEY	Uniquely identifies each row (combines NOT NULL and UNIQUE)
FOREIGN KEY	References a record in another table
CHECK	Validates data based on a specified condition
DEFAULT	Sets a default value if none is provided
CREATE INDEX	Speeds up data retrieval for indexed columns

---

Details and Examples of SQL Constraints

1. NOT NULL Constraint

Ensures that a column cannot contain NULL values.

CREATE TABLE Colleges (
  college_id INT NOT NULL,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);

In this example, college_id and college_code columns cannot have NULL values.

---

2. UNIQUE Constraint

Enforces unique values in a column, preventing duplicates.

CREATE TABLE Colleges (
  college_id INT NOT NULL UNIQUE,
  college_code VARCHAR(20) UNIQUE,
  college_name VARCHAR(50)
);

Here, both college_id and college_code must have unique values.

---

3. PRIMARY KEY Constraint

Combines NOT NULL and UNIQUE constraints to uniquely identify rows. Typically used as the main identifier for each row. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);
 
-- We can add name for it
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
-- the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

The college_id column is used to uniquely identify each row in the Colleges table.

We can add composite key

CREATE TABLE EmployeesPhones (
	EmpId Int,
	Phone CHAR(11),
	PRIMARY KEY(EmpId, Phone)
)

We can add it when we are creating table Create Tables We can add it after creating tables, ALTER ADD

-- Add it
ALTER TABLE Persons  
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
 
ALTER TABLE Persons  
ADD PRIMARY KEY (ID);
 
-- Drop it
ALTER TABLE Persons
DROP COnsTRAINT PK_Person;

---

4. FOREIGN KEY Constraint

Links a column in one table to the primary key of another table, ensuring referential integrity.

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  customer_id INT REFERENCES Customers(id)
);

In this example, customer_id in Orders references id in the Customers table, ensuring that each customer_id corresponds to an existing customer.

To add Foreign key

-- Inline Constraint
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
 
-- Out-of-line Constraint
ALTER TABLE Orders
ADD CONSTRAINT FK_Customer FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);
 
 
ALTER TABLE Orders
DROP FOREIGN KEY FK_Customer
 
-- On Delete | On Update
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);
 
-- Composite Foreign Key
CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
 
-- Add name for Constraint
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) 
    REFERENCES Customers(CustomerID)
);
 
-- With Null
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NULL,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

---

5. CHECK Constraint

Validates that column values meet a specific condition.

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  amount INT CHECK (amount >= 100)
);

The CHECK constraint ensures that amount in Orders is always greater than or equal to 100.

---

6. DEFAULT Constraint

Sets a default value for a column when no specific value is provided.

CREATE TABLE College (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20),
  college_country VARCHAR(20) DEFAULT 'US'
);

Here, if no value is specified for college_country, it defaults to “US”.

Salary FLOAT DEFAULT 10000
HiringDate DATETIME DEFAULT GETDATE()

---

7. CREATE INDEX Constraint

Creates an index to speed up data retrieval on a specific column.

CREATE TABLE Colleges (
  college_id INT PRIMARY KEY,
  college_code VARCHAR(20) NOT NULL,
  college_name VARCHAR(50)
);
 
-- Creating an index
CREATE INDEX college_index
ON Colleges(college_code);

The CREATE INDEX command creates an index on college_code in the Colleges table, which helps speed up data searches on that column.

---

Link to original