SQL - Data Definition

Data Definition

The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements:

• CREATE creates an object (a table, for example) in the database, e.g.:

CREATE TABLE My_table( my_field1 INT, my_field2 VARCHAR(50), my_field3 DATE NOT NULL, PRIMARY KEY (my_field1, my_field2) );

• ALTER modifies the structure of an existing object in various ways, for example, adding a column to an existing table or a constraint, e.g.:

ALTER TABLE My_table ADD my_field4 NUMBER(3) NOT NULL;

• TRUNCATE deletes all data from a table in a very fast way, deleting the data inside the table and not the table itself. It usually implies a subsequent COMMIT operation, i.e., it cannot be rolled back (data is not written to the logs for rollback later, unlike DELETE).

TRUNCATE TABLE My_table;

• DROP deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back, e.g.:

DROP TABLE My_table;