Data Definition Language (DDL) in SQL Server

Mannan Ul Haq
personMannan Ul Haq
August 13, 2024
0

Data Definition Language (DDL)

Data Definition Language (DDL) statements are used in SQL Server to define and manage database structures. These include creating, modifying, and deleting databases, tables, and other objects. Below, we'll explore some fundamental DDL statements.


Create and Drop Statements

1. Create Database:

The CREATE DATABASE statement is used to create a new database. Think of it as laying the foundation for your data by giving it a name and creating a place where you can store tables and other objects.

Syntax:

CREATE DATABASE database_name;

Explanation:

  • CREATE DATABASE: Instructs SQL Server to create a new database.
  • database_name: The name you want to give to your database.

Example:

CREATE DATABASE UniversityDB;

This command creates a new database named UniversityDB.


2. Drop Database:

The DROP DATABASE statement is used to delete an existing database, effectively removing all the data and objects within it.

Syntax:

DROP DATABASE database_name;

Explanation:

  • DROP DATABASE: Instructs SQL Server to delete a database.
  • database_name: The name of the database you want to remove.

Example:

DROP DATABASE UniversityDB;

This command deletes the UniversityDB database.


3. Create Table:

The CREATE TABLE statement is used to define a new table, specifying the columns and their data types.

Syntax:

CREATE TABLE table_name (
    column1 datatype1,
    column2 datatype2,
    ...
);

Explanation:

  • CREATE TABLE: Tells SQL Server to create a new table.
  • table_name: The name of the table.
  • (column1 datatype1, ...): Specifies the columns and their data types.

Example:

CREATE TABLE Students (
    StudentID INT,
    Name VARCHAR(50),
    Age INT,
    GPA FLOAT
);

This command creates a Students table with columns for StudentID, Name, Age, and GPA.

Below is a table listing some important data types commonly used in SQL databases:

Data Type Description Example
INT Integer (whole number) 123, -45, 0
VARCHAR(n) Variable-length string with maximum length of n 'Hello', '12345', 'SQL'
CHAR(n) Fixed-length string with exactly length of n 'John ', 'ABCD'
FLOAT Floating-point number 3.14, -0.001, 123.456
DATE Date (year, month, day) '2024-01-23', '1999-12-31'
TIME Time (hour, minute, second, fraction) '12:30:45.678', '09:00:00'


4. Drop Table:

The DROP TABLE statement is used to delete an existing table, removing all the data stored within it.

Syntax:

DROP TABLE table_name;

Explanation:

  • DROP TABLE: Instructs SQL Server to delete a table.
  • table_name: The name of the table to remove.

Example:

DROP TABLE Students;

This command deletes the Students table.


5. Truncate Table:

The TRUNCATE TABLE statement is used to remove all rows from a table without deleting the table structure itself.

Syntax:

TRUNCATE TABLE table_name;

Explanation:

  • TRUNCATE TABLE: Clears all rows from a table.
  • table_name: The name of the table to clear.

Example:

TRUNCATE TABLE Students;

This command removes all rows from the Students table but keeps the table structure intact.


Alter Table Statements

1. Add Column:

The ALTER TABLE ADD COLUMN statement is used to add a new column to an existing table.

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Explanation:

  • ALTER TABLE: Tells SQL Server to modify a table.
  • table_name: The name of the table.
  • ADD COLUMN: Specifies that a new column should be added.
  • column_name: The name of the new column.
  • datatype: The data type of the new column.

Example:

ALTER TABLE Students
ADD Email VARCHAR(100);

This command adds a new column Email to the Students table.


2. Drop Column:

The ALTER TABLE DROP COLUMN statement is used to remove a column from an existing table.

Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

Explanation:

  • ALTER TABLE: Tells SQL Server to modify a table.
  • table_name: The name of the table.
  • DROP COLUMN: Specifies that a column should be removed.
  • column_name: The name of the column to remove.

Example:

ALTER TABLE Students
DROP COLUMN Email;

This command removes the Email column from the Students table.


3. Modify Column Data Type:

The ALTER TABLE ALTER COLUMN statement is used to change the data type of a column in an existing table.

Syntax:

ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;

Explanation:

  • ALTER TABLE: Tells SQL Server to modify a table.
  • table_name: The name of the table.
  • ALTER COLUMN: Specifies that a column's data type should be changed.
  • column_name: The name of the column.
  • new_datatype: The new data type for the column.

Example:

ALTER TABLE Students
ALTER COLUMN Age INT;

This command changes the data type of the Age column to INT in the Students table.


Constraints Statements

1. Not Null:

The NOT NULL constraint ensures that a column cannot have a NULL value.

On Create Table:

Syntax:

CREATE TABLE TableName (
    Column1 DataType NOT NULL,
    Column2 DataType NOT NULL,
    ...
);

Example:

CREATE TABLE Students (
    StudentID INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Age INT
);

On Alter Table:

Syntax:

ALTER TABLE TableName
ALTER COLUMN ColumnName DataType NOT NULL;

Example:

ALTER TABLE Students
ALTER COLUMN Age INT NOT NULL;


2. Unique:

The UNIQUE constraint ensures that all values in a column are different.

On Create Table:

Syntax:

CREATE TABLE TableName (
    Column1 DataType UNIQUE,
    Column2 DataType,
    ...
);

Example:

CREATE TABLE Employees (
    EmployeeID INT NOT NULL UNIQUE,
    Name VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

For Multiple Columns:

Syntax:

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...
    CONSTRAINT ConstraintName UNIQUE (Column1, Column2, ...)
);

Example:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    CONSTRAINT UniqueOrderProduct UNIQUE (OrderID, ProductID)
);

On Alter Table:

Syntax:

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName UNIQUE (ColumnName);

Example:

ALTER TABLE Employees
ADD CONSTRAINT UniqueEmail UNIQUE (Email);


3. Primary Key:

The PRIMARY KEY constraint uniquely identifies each row in a table.

On Create Table:

Syntax:

CREATE TABLE TableName (
    Column1 DataType PRIMARY KEY,
    Column2 DataType,
    ...
);

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

For Multiple Columns:

Syntax:

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...
    CONSTRAINT ConstraintName PRIMARY KEY (Column1, Column2, ...)
);

Example:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, ProductID)
);

On Alter Table:

Syntax:

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName);

Example:

ALTER TABLE Students
ADD CONSTRAINT PK_Students PRIMARY KEY (StudentID);


4. Foreign Key:

The FOREIGN KEY constraint links two tables, ensuring referential integrity.

On Create Table:

Syntax:

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...
    column_name datatype REFERENCES ReferencedTable(ReferencedColumn)
);

Example:

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    OrderNumber INT NOT NULL,
    PersonID INT REFERENCES Persons(PersonID)
);

For Multiple Columns:

Syntax:

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...
    CONSTRAINT ConstraintName FOREIGN KEY (Column1, Column2, ...) REFERENCES ReferencedTable(ReferencedColumn1, ReferencedColumn2, ...)
);

Example:

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    CONSTRAINT OrderDetails_FK FOREIGN KEY (OrderID, ProductID) REFERENCES Orders(OrderID, ProductID)
);

On Alter Table:

Syntax:

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName FOREIGN KEY (ColumnName) REFERENCES ReferencedTable(ReferencedColumn);

Example:

ALTER TABLE OrderDetails
ADD CONSTRAINT OrderDetails_FK FOREIGN KEY (ProductID) REFERENCES Products(ProductID);


5. Check:

The CHECK constraint ensures that values in a column satisfy a specific condition.

On Create Table:

Syntax:

CREATE TABLE TableName (
    Column1 DataType CHECK (Condition),
    Column2 DataType,
    ...
);

Example:

CREATE TABLE Employees (
    EmployeeID INT,
    Age INT CHECK (Age >= 18),
    Department VARCHAR(50)
);

For Multiple Columns:

Syntax:

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...
    CONSTRAINT ConstraintName CHECK (Condition1, Condition2, ...)
);

Example:

CREATE TABLE Employees (
    EmployeeID INT,
    Age INT,
    Salary FLOAT,
    CONSTRAINT CHK_Employee CHECK (Age >= 18 AND Salary >= 0)
);

On Alter Table:

Syntax:

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (Condition);

Example:

ALTER TABLE Employees
ADD CONSTRAINT CHK_EmployeeSalary CHECK (Salary >= 0);


6. Default:

The DEFAULT constraint sets a default value for a column if no value is provided.

On Create Table:

Syntax:

CREATE TABLE TableName (
    Column1 DataType DEFAULT DefaultValue,
    Column2 DataType DEFAULT DefaultValue,
    ...
);

Example:

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50) DEFAULT 'Unknown'
);

On Alter Table:

Syntax:

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(50),
    Department VARCHAR(50) DEFAULT 'Unknown'
);

Example:

ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_Department DEFAULT 'Unknown' FOR Department;


Drop a Constraint:

To drop a constraint, we use the ALTER TABLE statement along with the DROP CONSTRAINT clause.

Syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example:

Suppose you have a table called Employees with a unique constraint on the Email column, you can drop the unique constraint like this:

ALTER TABLE Employees
DROP CONSTRAINT UQ_Employees_Email;


Add Auto Increment Field

In SQL Server, an auto-increment field is known as an identity column. It automatically generates sequential numeric values for new rows.

Syntax:

CREATE TABLE TableName (
    ColumnName DataType IDENTITY(SeedValue, IncrementValue),
    ...
);

Example:

CREATE TABLE Students (
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);


Tags
Mannan Ul Haq

Mannan Ul Haq

I'm Mannan Ul Haq, a passionate Data Science enthusiast and dedicated student at FAST-NUCES Lahore, Pakistan. Join me in exploring the exciting world of Computer Science as I teach and guide you through beginner tutorials to advanced concepts. Let's connect, share ideas, and grow together in this dynamic community. facebook instagram linkedin

    Post a Comment

    0Comments

    Post a Comment (0)

    #buttons=(Accept !) #days=(20)

    Our website uses cookies to enhance your experience. Check Now
    Accept !
    Share to other apps
    Copy Post Link