Data Definition Lanage(DDL)

2020, May 23    

Data Definition Language(DDL)

Used to create or modify database schema.

CREATE

Create database
CREATE DATABASE database_name
Create table
CREATE TABLE TABLE_NAME(
column_name1 datatype1,
column_name2 datatype2,
column_name3 datatype3
);

ALTER

Add a column
ALTER TABLE table_name ADD column\_name datatype;
Rename existing column
ALTER TABLE table_name RENAME old_column_name TO new_column_name;
Drop a column
ALTER TABLE table_name DROP COLUMN column_name;
Modify the size of the column or change datatype of the column

SQL Server:

ALTER TABLE table_name ALTER COLUMN column_name, datatype;

MySQL:

ALTER TABLE table_name MODIFY COLUMN column_name, datatype;

DROP

Remove a database
DROP DATABASE database_name;
Remove a table from the database.
DROP TABLE table_name;

TRUNCATE

clear a table

TRUNCATE TABLE table_name;

RENAME

RENAME TABLE old_name TO new_name;

Constraints

Specify rules for data in a table

SQL Create Constraints

Constraints can be specified when the table is created or after the table is created.

Constraints can be column level or table level.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);
NOT NULL

Enforce a column to not accept null values

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

ALTER TABLE Persons
MODIFY Age int NOT NULL;
UNIQUE

Ensures that all values in a column are different

SQL Server

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

MYSQL

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

MYSQL/SQL Server on multiple columns

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

Alter table


ALTER TABLE Persons
ADD UNIQUE (ID);


ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

Drop

MySQL:
ALTER TABLE Persons
DROP INDEX UC_Person;

SQL Server:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
PRIMARY KEY

Automatically embed Unique constraint

Create table MySQL

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

SQL Server

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

Naming a constraint

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Alter Table

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Drop

ALTER TABLE Persons
DROP PRIMARY KEY;

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
FOREIGN KEY

Create Table

MySQL

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

SQL Server

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

Naming a constraint

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

ALTER table

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Naming a constraint

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Drop a foreign key

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
CHECK

limit the value range

Create table

MySQL

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

SQL Server

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

Alter table

ALTER TABLE Persons
ADD CHECK (Age>=18);

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

Drop SQL Server

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

MySQL

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
DEFAULT

provide a default value for a column Create table

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

Alter table MySQL

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

SQL Server

ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;

Drop MySQL

ALTER TABLE Persons
ALTER City DROP DEFAULT;

SQL Server

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
INDEX

Quickly retrive data

duplicate values are allowed

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Unique Index

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Drop index SQL Server

DROP INDEX table_name.index_name;

MySQL

ALTER TABLE table_name
DROP INDEX index_name;
Twitter Facebook Google+