DDL — Data Definition Language

1. SQL Overview

Category	Statements	Purpose
DDL	CREATE, ALTER, DROP	Structure of database objects
DQL	SELECT	Querying data
DML	INSERT, UPDATE, DELETE	Manipulating data
DCL	GRANT, REVOKE	Managing permissions

---

2. Creating Tables

Schema & dbo

• Schema = namespace for database objects
• dbo = default schema (database owner)
• Notation: schema.table e.g. dbo.Customers

Temporary Tables

Type	Syntax	Visible to
Local	#tablename	Current session
Global	##tablename	All sessions

Via SSMSVia T-SQL

Object Explorer → database → Tables → right-click → New Table

USE ClassicModels;
GO

CREATE TABLE dbo.Customers (
    customerNumber INT           NOT NULL,
    customerName   VARCHAR(50)   NOT NULL,
    contactName    VARCHAR(50)   NOT NULL,
    city           VARCHAR(50),
    country        VARCHAR(50),
    creditLimit    DECIMAL(10,2)
);
GO

---

3. Modifying Tables

Via SSMSVia T-SQL

Object Explorer → table → right-click → Design

-- Add a column
ALTER TABLE dbo.Customers
ADD postalCode VARCHAR(15);

-- Modify a column (datatype/length)
ALTER TABLE dbo.Customers
ALTER COLUMN customerName VARCHAR(100) NOT NULL;

-- Remove a column
ALTER TABLE dbo.Customers
DROP COLUMN postalCode;

---

4. Deleting Tables

DROP TABLE dbo.Customers;

Warning

Deleting a table with an FK constraint causes an error. Remove the child table or the constraint first.

---

5. Querying Table Info

-- Overview of columns, constraints, keys
EXEC sp_help 'dbo.Customers';

-- Overview of indexes
EXEC sp_helpindex 'dbo.Customers';

Via SSMS: right-click the table → Script Table as → CREATE To

---

6. Data Types

Type	Description
CHAR(n)	Fixed-length text — unused space is padded with spaces
VARCHAR(n)	Variable-length text — max n characters
TEXT	Long text
TINYINT	0 – 255
SMALLINT	-32,768 – 32,767
INT	-2,147,483,648 – 2,147,483,647
BIGINT	Very large integer
DECIMAL(p,s)	Decimal number — p = total digits, s = digits after the decimal
DATE	Date (YYYY-MM-DD)
DATETIME	Date + time
TIMESTAMP	Automatic timestamp

Creating Custom Data Types

-- Custom type based on an existing type
CREATE TYPE PostalCode FROM VARCHAR(10) NOT NULL;

---

7. NULL Values

• NULL = unknown — not the same as 0 or an empty string
• NULL + 1 = NULL — any calculation with NULL gives NULL
• NULL = NULL is FALSE — always use IS NULL

-- Searching for NULL
SELECT * FROM customers WHERE creditLimit IS NULL;

-- Replacing NULL with a default value
SELECT customerName, COALESCE(creditLimit, 0) AS creditLimit
FROM customers;

---

8. Data Integrity

Type	Description
Declarative	Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL)
Procedural	Triggers — code that runs automatically on INSERT/UPDATE/DELETE

---

9. Declarative Integrity Constraints

Primary Key

• Uniquely identifies each record via 1 or more columns
• Only unique, non-NULL values
• Every table has at most 1 primary key

-- Inline
CREATE TABLE Persons (
    ID        INT          NOT NULL PRIMARY KEY,
    LastName  VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age       INT
);

-- As a named constraint
CREATE TABLE Person (
    ID        INT          NOT NULL,
    LastName  VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age       INT,
    CONSTRAINT PK_Person PRIMARY KEY (ID)
);

Unique Constraint

• Similar to a PK but multiple are allowed per table
• NULL values are allowed (unlike a PK)

-- Inline
CREATE TABLE Persons (
    ID       INT          NOT NULL UNIQUE,
    LastName VARCHAR(255) NOT NULL
);

-- As a named constraint
CREATE TABLE Persons (
    ID       INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    CONSTRAINT UC_Person UNIQUE (ID)
);

Check Constraint

• Restricts the possible values — a record isn't added if the condition isn't met

-- Single
CREATE TABLE Persons (
    Age INT CHECK (Age >= 18)
);

-- Multiple, as a named constraint
CREATE TABLE Persons (
    Age  INT,
    City VARCHAR(255),
    CONSTRAINT CHK_Person CHECK (Age >= 18 AND City = 'Antwerp')
);

Foreign Key

• References the PK of another table
• The table with the FK = the child table
• The table with the PK = the parent table / referenced table

-- Inline
CREATE TABLE Orders (
    OrderID     INT NOT NULL PRIMARY KEY,
    OrderNumber INT NOT NULL,
    PersonID    INT FOREIGN KEY REFERENCES Persons(PersonID)
);

-- As a named constraint
CREATE TABLE Orders (
    OrderID     INT NOT NULL,
    OrderNumber INT NOT NULL,
    PersonID    INT,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY REFERENCES Persons(PersonID)
);

Referential Integrity

On an INSERT or UPDATE with an FK that doesn't exist → error:

UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FKemployee'.
The conflict occurred in database 'sample', table 'employee', column 'dept_no'.

4 options for behavior on a change/deletion in the parent table:

Option	Behavior
NO ACTION	Only allows changes in the parent table that don't affect the child — otherwise an error
CASCADE	A change/deletion in the parent table → automatically applied in the child table
SET NULL	A change/deletion in the parent → the FK in the child is set to NULL
SET DEFAULT	A change/deletion in the parent → the FK in the child is set to its default value

CONSTRAINT FK_PersonOrder
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
    ON DELETE CASCADE
    ON UPDATE SET NULL

---

10. Renaming Database Objects

USE ClassicModels;
GO

-- Rename a table
EXEC sp_rename @objname = 'Offices', @newname = 'Department';

-- Rename a column
EXEC sp_rename @objname = 'Employees.jobTitle', @newname = 'title';

Also works for: database, view, stored procedure.