The Data Definition Language is the part of the SQL language that allows you to perform operations on the objects that make up the structure of a database (i.e. the container). These objects are TABLES, VIEWS or INDEX. (The present section focuses on the manipulation of tables and indexes, the views are discussed in a later section).
The possible operations with the DDL are the creation of objects, the removal of objects (delete) and their modification (subject to certain reservations).
Create a table
When creating a table, one must identify it (give it a name) and define its structure, i.e. the columns that compose it.
CREATE TABLE table (data type column [NOT NULL], …);The expression NOT NULL means that when adding a new row to the table, the column must have a value. The term NULL in this column will not be tolerated.
Create a table that allows you to save invoices.
CREATE TABLE Invoice (invoice_number VARCHAR (10) NOT NULL, date_bill DATETIME NOT NULL, customer_name VARCHAR (40), amount DECIMAL (8,2));The primary key constraint (PRIMARY KEY)
The PRIMARY KEY clause is used to identify the primary key of the table. Primary keys always have the characteristics of being obligatory and unique. This constraint makes it possible to ensure compliance with these characteristics. Note that there is always only one primary key per table.
Create a table that allows you to save your buddy’s phone list. Consider that the
name and date of birth are a valid primary key.
CREATE TABLE Buddy(
last_name VARCHAR (30) NOT NULL,
first_name VARCHAR (30) NOT NULL,
date_birth DATETIME,
phone VARCHAR (10),
CONSTRAINT Buddy_pk PRIMARY KEY (last_name, birth_date));Thus defined, the structure of the table requires a value for the ‘last_name’ and ‘date_birth’ columns, and the combined value of these columns must be unique for all rows in the table.
The constraint of uniqueness (UNIQUE)
When some values must be unique without the primary key, a unique constraint can be used. This constraint ensures that the value is unique for the entire table, but null values are allowed.
CONSTRAINT UNIQUE constraint (list of columns)The referential integrity constraint (FOREIGN KEY)
If the table contains a remote key, it is possible to add a clause that defines a referential integrity constraint with the table referred to by the foreign key.
CONSTRAINT constraint FOREIGN KEY (list of columns) REFERENCES table [(list of columns)]When a new row is added to the table where a referential constraint has been defined, the RDBMS checks whether the value of the remote key already exists as the primary key value in the reference table.
Define a referential integrity constraint between the Project table and the Leader
table.
CREATE TABLE Project (
project_code VARCHAR (5) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
emp_no VARCHAR (3),
CONSTRAINT Project_pk PRIMARY KEY (project_code),
CONSTRAINT Project_fk_Chef FOREIGN KEY (emp_no)
REFERENCES Leader);By default, the constraint is set on the primary key of the reference table. If one wishes to establish the constraint on another value (columns) of the reference table then, in the FOREIGN KEY clause, it is necessary to provide the name or names of the columns being the reference.
Delete a table
A table is deleted using the DROP TABLE statement.
DROP TABLE table;When a table is erased, other objects associated with this table are likely to disappear at the same time (such as indexes).
Modify a table
You can modify the structure of an existing table. The SQL standard is limited on the nature of the allowed changes (it does not allow the modification of the data type of a column, for example), but several DBMS on the market offer greater possibilities. The following syntax is that of SQL Server.
Note that changes made to a table that already contains data may be restricted. The desired transformation must be applicable to the existing data.
Essentially, the changes are for two things: the columns of the table, or its constraints.
To modify the columns:
ALTER TABLE table ADD column column_definition;
ALTER TABLE table DROP COLUMN column;
ALTER TABLE table ALTER COLUMN column column_definition;
To modify the constraints:
ALTER TABLE table ADD CONSTRAINT constraint constraint_definition;
ALTER TABLE table DROP CONSTRAINT constraint;Note that it is not possible to modify a constraint, it must be removed and recreated with a new form.
Add a column to the Leader table to record the project manager’s phone number.
ALTER TABLE Leader ADD telephone VARCHAR (10);Creating an index
SQL does not require a user to know the existence of indexes to manipulate a database. There is no clause in any data manipulation statement to specify the use of an index. The RDBMS decides for itself whether the query composed by the user requires the use of an index. Indexes are required to ensure adequate database performance and are part of the design decisions that a database administrator must make.
The CREATE INDEX statement is used to add an index to the database.
CREATE [UNIQUE] INDEX index ON table (list of columns that make up the index);Note:
If the UNIQUE keyword is included, then the index will check that each entry in the index is unique. It will be impossible to add a new row in the table that would cause the duplication of one of the entries in the index. Typically, the use of the UNIQUE character corresponds to the definition of a primary key, or an alternative key.
Clear an index
You delete an index the same way you delete a table, i.e. by the DROP statement.
DROP INDEX index;