Creating a table in SQL
Server involves defining the structure of the table, including the columns,
their data types, constraints, and any other necessary options. Below, I'll
provide a detailed explanation of creating a table with various elements such as
primary keys, foreign keys, indexes, and constraints.
Basic Syntax:
CREATE TABLE TableName (
Column1 DataType Constraints,
Column2 DataType Constraints,
...
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Primary Key
FirstName NVARCHAR(50) NOT NULL, -- Not Null constraint
LastName NVARCHAR(50) NOT NULL,
BirthDate DATE,
HireDate DATE,
Salary DECIMAL(18, 2)
);
Detailed Table Creation with Constraints and Indexes
Creating a Table with Primary Key:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY, -- Primary Key
DepartmentName NVARCHAR(50) NOT NULL
-- Not Null constraint
);
Creating a Table with Foreign Key:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Primary Key
FirstName NVARCHAR(50) NOT NULL, -- Not Null constraint
LastName NVARCHAR(50) NOT NULL,
DepartmentID INT, -- Foreign Key
CONSTRAINT FK_Department FOREIGN KEY (DepartmentID)
REFERENCES Departments (DepartmentID) -- Foreign Key constraint
);
Creating a Table with Unique Constraint:
CREATE TABLE Users (
UserID INT PRIMARY KEY, -- Primary Key
UserName NVARCHAR(50) NOT NULL UNIQUE
-- Unique constraint
);
Creating a Table with Check Constraint:
CREATE TABLE Products (
ProductID INT PRIMARY KEY, -- Primary Key
ProductName NVARCHAR(100) NOT NULL,
Price DECIMAL(18, 2) CHECK (Price > 0) -- Check constraint
);
Creating a Table with Default Constraint:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY, -- Primary Key
OrderDate DATE DEFAULT GETDATE(),
-- Default constraint
Quantity INT DEFAULT 1
);
Creating a Table with Index:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY, -- Primary Key
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Email NVARCHAR(100)
);
-- Creating an
index on the Email column
CREATE INDEX IX_Customers_Email ON Customers (Email);
Composite Keys and Composite Indexes
Composite Primary Key:
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID) -- Composite Primary Key
);
Composite Index:
sql
Copy code
CREATE TABLE Sales (
SaleID INT PRIMARY KEY,
ProductID INT,
SaleDate DATE,
Quantity INT
);
-- Creating a
composite index on ProductID and SaleDate
CREATE INDEX IX_Sales_ProductID_SaleDate ON Sales (ProductID, SaleDate);
Full Example with All Elements
CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY, -- Identity column and Primary Key
FirstName NVARCHAR(50) NOT NULL, -- Not Null constraint
LastName NVARCHAR(50) NOT NULL,
BirthDate DATE CHECK (BirthDate < GETDATE()), -- Check constraint
HireDate DATE DEFAULT GETDATE(), -- Default constraint
Salary DECIMAL(18, 2) CHECK (Salary > 0), -- Check constraint
DepartmentID INT, -- Foreign Key
CONSTRAINT FK_Department FOREIGN KEY (DepartmentID)
REFERENCES Departments (DepartmentID), -- Foreign Key constraint
CONSTRAINT
UQ_EmployeeName UNIQUE (FirstName, LastName) -- Unique constraint
);
-- Creating an
index on the Salary column
CREATE INDEX IX_Employees_Salary ON Employees (Salary);