Use SQL to create Database Tables

Once you have created a schema to hold your tables, you can enter the SQL statements to create those tables. Following is the format of the SQL statement used to create a table:

CREATE TABLE TableName
( Column01Name Column01DataType Column01Constraints 
Column02Name Column02DataType Column02Constraints…
 PRIMARY KEY (ColumnXX) FOREIGN KEY (ColumnXX))

Indenting the column and other statements is not mandatory, but it does make the table's specification easier to read.
The ColumnName part of the statement is self-explanatory. The ColumnDataType depends on the type of data the column will store. A partial list of data types (or data domains) was presented earlier in this course. Again, the list of available domains will not be the same for every RDBMS. The final element lists the constraints on the column. Possible constraints for a column include whether it has a default value or any validation rules (e.g., the field must contain a value greater than zero). The next lesson explains how to use SQL to build the database.

The CREATE TABLE command is used to specify a new relation by giving it a name and specifying its attributes and initial constraints. The attributes are specified first, and each attribute is given a name, a data type to specify its domain of values, and any attribute constraints, such as NOT NULL. The key, entity integrity, and referential integrity constraints can be specified within the CREATE TABLE statement after the attributes are declared, or they can be added later using the ALTER TABLE command. Figure 6-13 shows sample data definition statements in SQL for the COMPANY relational database schema. Typically, the SQL schema in which the relations are declared is implicitly specified in the environment in which the CREATE TABLE statements are executed. Alternatively, we can explicitly attach the schema name to the relation name, separated by a period. For example, by writing

CREATE TABLE COMPANY.EMPLOYEE ...

CREATE TABLE EMPLOYEE ...

We can explicitly (rather than implicitly) make the EMPLOYEE table part of the COMPANY schema. The relations declared through CREATE TABLE statements are called base tables (or base relations); this means that the relation and its tuples are actually created and stored as a file by the DBMS. Base relations are distinguished from virtual relations, created through the CREATE VIEW statement, which may or may not correspond to an actual physical file. In SQL, the attributes in a base table are considered to be ordered in the sequence in which they are specified in the CREATE TABLE statement. However, rows (tuples) are not considered to be ordered within a relation.
It is important to note that in Figure 4.1, there are some foreign keys that may cause errors because they are specified either via circular references or because they refer to a table that has not yet been created. For example, the foreign key Super_ssn in the EMPLOYEE table is a circular reference because it refers to the table itself. The foreign key Dno in the EMPLOYEE table refers to the DEPARTMENT table, which has not been created yet. To deal with this type of problem, these constraints can be left out of the initial CREATE TABLE statement, and then added later using the ALTER TABLE statement.