Creating and Modifying Tables

Creating and modifying tables are fundamental operations in SQL that allow you to define the structure of your database and adapt it as your needs change. This section will cover the key concepts and commands for creating and modifying tables.

1. Creating Tables

Creating a table involves defining its structure, including the columns, data types, and constraints. The CREATE TABLE statement is used to create a new table in the database.

Example:

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

In this example, a table named Employees is created with columns for EmployeeID, FirstName, LastName, Department, and HireDate. The EmployeeID column is designated as the primary key.

2. Modifying Tables

Modifying a table involves changing its structure, such as adding or removing columns, altering data types, or adding constraints. The ALTER TABLE statement is used to modify an existing table.

Example of adding a column:

ALTER TABLE Employees
ADD COLUMN Email VARCHAR(100);
    

This command adds a new column named Email to the Employees table.

Example of modifying a column:

ALTER TABLE Employees
ALTER COLUMN Department VARCHAR(100);
    

This command changes the data type of the Department column to VARCHAR(100).

Example of dropping a column:

ALTER TABLE Employees
DROP COLUMN HireDate;
    

This command removes the HireDate column from the Employees table.

3. Constraints

Constraints are rules applied to the columns of a table to ensure data integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

Example of adding a primary key constraint:

ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);
    

This command adds a primary key constraint to the EmployeeID column.

Example of adding a foreign key constraint:

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);
    

This command adds a foreign key constraint to the CustomerID column in the Orders table, referencing the CustomerID column in the Customers table.

4. Dropping Tables

Dropping a table involves removing the entire table from the database. The DROP TABLE statement is used to delete a table.

Example:

DROP TABLE Employees;
    

This command removes the Employees table from the database.