Creation and modification of tables

Creation and modification of tables

In this unit, we will show you how to create, modify, and delete tables using SQL commands.
Create tables

With CREATE TABLEa new table can be created in a database. The command has the following basic structure:

CREATE TABLE <table name> (<Attribute definitions and constraints>);

The table name must be unique within the current database or the current scheme.
Attribute definition

Attributes are defined by a name and a datatype, whereas the name must be unique within the table. These specifications are compulsory for all attributes.
The order of the attributes at definition corresponds to the order of the columns in the table created. If a certain order is aspired, you need to define it at the creation of the table. Unless there are no changes made to the table (see Change table structure, the order stays this way.

There are two types of constraints: table constraints and attribute constraints. The difference is that attribute restrictions apply to only one attribute whereas table constraints may apply to more than one attribute but this need not be. With these restrictions, the range of values ​​of the attributes can be restricted or it is prevented that the entered values ​​are not allowed. A record cannot be recorded if it violates a restriction.
There are four kinds of constraints:

    UNIQUE - the attribute or the attribute combination need to be unique within the table
    PRIMARY KEY - the attribute or the attribute combination is the primary key of the table
    FOREIGN KEY - the attribute is a foreign key
    CHECK - Condition that must be fulfilled for an attribute or an attribute combination

The constraints can be named. However, this is not necessary.

In this example, a table is added to a database. That table is linked to an already existing table. The difference between an attribute and a table constraint can be seen in the SQL command. projekt_ID and leiter_ID have an attribute constraint (the constraint is written directly behind the attribute definition). projekt_ID has the constraint PRIMARY KEY - it is therefore the primary key of this table, i.e. the attribute must be unique and must not be NULL. leiter_ID has the constraint NOT NULL (special case of a CHEK constraint), meaning it needs to hold a value at all times.
The link to the existing table is defined as a table constraint (FOREIGN KEY) and is named (projektleiter). This constraint could also be defined as an attribute constraint since it only includes one attribute.
The example shows that there are basically no difference between attribute and table constraints as long as only one attribute is affected. It is about two different ways of collecting constraints.
Changing the table structure

With ALTER TABLE the structure of a table can be modified. The attributes and constraints that were created with CREATE TABLE can be modified, new ones can be added, and existing ones can be deleted. The command has the following syntax:

ALTER TABLE <table name> <Change> ;

whereas <Change> can include various commands:

    ADD [COLUMN] <Attribute defintion>
    Add an attribute (Attribute definiton as in CREATE)
    ALTER [COLUMN] <Attribute name> SET DEFAULT <Standard value>
    define a new standard value
    ALTER [COLUMN] <Attribute name> DROP DEFAULT
    delete current standard value
    DROP [COLUMN] <Attribute name> {RESTRICT | CASCADE}
    delete an attribute
    ADD <Table constraint>
    add new table constraint (table constraint as in CREATE)
    DROP CONSTRAINT <Table constraint>
    delet a table constraint

With the above commands, attributes and constraints can be added or deleted respectively. In addition, standard values for the attributes can be set or deleted. There are other SQL commands that are not listed here.
Default SQL does not include any commands for modification or renaming of attributes. This would lead to problems when data already exists. However, in some databases these commands are included (e.g. MODIFY or RENAME). The syntax is different in every system though. If there are no data, the attribute to be changed can be delete and reattached.

In this example, an attribute is added to a table. The dataset shown contains NULL for this attribute because there was no value assigned yet. Afterwards, this attribute is delted from the table again. The keyword RESTRICT provokes that only attributes that are not linked to other tables can be deleted (foreign key). Alternatively, the keyword CASCADE can be used. Using this, not only the designated column but also the linked column in the other table is delted.