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.

Constraints

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.