Data Definition Language (SQL DDL)

The statement for table creation is  CREATE TABLE:

CREATE TABLE < name of the TABLE > (< list of COLUMN specifications AND constraints >);

A column specification includes the column name and the data type valid for the column. Furthermore, certain constraints can be defined not only via extra specifications, but within the column specification itself. Examples are the (non-compound) primary and foreign key constraints, as well as the NOT NULL constraint.

We will study the table-related aspects of the language using the educational databases.

The Product table of the Computer firm database consists of three columns – maker, model, and type; all of them have the string data type VARCHAR(N). To create this table, we could use the following statement:

CREATE TABLE Product (maker varchar(10), model varchar(50), type varchar(50));

N is the maximal number of characters the data in the corresponding column can contain. VARCHAR is a variable-length type; this means that, if we specify a value with less than N characters, the very number of characters specified will be stored in the system. Alternatively, the fixed-length string type CHAR(N) can be used, which is right-padded with spaces to the specified length when stored. In other words, there are always exactly N characters stored in the system for this data type.

If N is not specified, it is assumed to be 1 (single character) by default.

As soon as the table has been created, data can be written to it using the INSERT statement . Let’s do that: 

INSERT INTO product VALUES
('A',    '1232',    'PC'),
('A',    '1232',    'Printer'),
(NULL,    NULL,    NULL)

The data has been successfully added, but it seems to be not quite right somehow. First, it’s not clear whether the model 1232 is a printer or a PC. Then, we have another model, about which nothing is known at all.

Here we need to make a short digression to emphasize that by creating tables, we establish a relational model of the subject area. Our subject area is stock management of a computer company. To make sure the data model is adequate to the subject area, the tables have to be defined in such a way that things happening to objects in the real world can be simulated by the model, while things not possible in the subject area shouldn’t occur in the model, either.

Thus, in reality a model can’t be a printer and a PC at the same time, as it turned out to be in our case. This is where we approach the concept of data integrity and its enforcement by applying constraints.