CREATE TABLE: A Basic Form

The CREATE TABLE statement creates a new base table with all corresponding columns and their data types. The basic form of the CREATE TABLE statement is

CREATE TABLE table_name
	(col_name1 type1 [NOT NULL| NULL]
	[{, col_name2 type2 [NOT NULL| NULL]} ...])Code language: PHP (php)

table_name is the name of the created base table. The maximum number of tables per database is limited by the number of objects in the database (there can be more than 2 billion objects in a database, including tables, views, stored procedures, triggers, and constraints). col_name1, col_name2,… are the names of the table columns. type1, type2,… are data types of corresponding columns.

The first constraint that will be discussed in this tutorial is the existence and nonexistence of NULL values within a column. If NOT NULL is specified, the assignment of NULL values for the column is not allowed. (In that case, the column may not contain NULLs, and if there is a NULL value to be inserted, the system returns an error message.)

As already stated, a database object (in this case, a table) is always created within a schema of a database. A user can create a table only in a schema for which she has ALTER permissions. Any user in the sysadmin, db_ddladmin, or db_owner role can create a table in any schema.

The creator of a table must not be its owner. This means that you can create a table that belongs to someone else. Similarly, a table created with the CREATE TABLE statement must not belong to the current database if some other (existing) database name, together with the schema name, is specified as the prefix of the table name.

The schema to which a table belongs has two possible default names. If a table is specified without the explicit schema name, the system checks for a table name in the corresponding default schema. If the object name cannot be found in the default schema, the system searches in the dbo schema.

Temporary tables are a special kind of base table. They are stored in the tempdb database and are automatically dropped at the end of the session. The properties of temporary tables and examples concerning them are given in Chapter “Queries”. Example 5.4 shows the creation of all tables of the sample database. (The sample database should be the current database.)

USE sample;
CREATE TABLE employee	(emp_no INTEGER NOT NULL,
		emp_fname CHAR(20) NOT NULL,
		emp_lname CHAR(20) NOT NULL,
		dept_no CHAR(4) NULL);

CREATE TABLE department	(dept_no CHAR(4) NOT NULL,
		dept_name CHAR(25) NOT NULL,
		location CHAR(30) NULL);

CREATE TABLE project	(project_no CHAR(4) NOT NULL,
		project_name CHAR(15) NOT NULL,
		budget FLOAT NULL);

CREATE TABLE works_on	(emp_no INTEGER NOT NULL,
		project_no CHAR(4) NOT NULL,
		job CHAR (15) NULL,
		enter_date DATE NULL);Code language: PHP (php)

Besides the data type and the nullability, the column specification can contain the following options:

• DEFAULT clause
• IDENTITY property

The DEFAULT clause in the column definition specifies the default value of the column—that is, whenever a new row is inserted into the table, the default value for the particular column will be used if there is no value specified for it. A constant value, such as the system functions USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP, and NULL, among others, can be used as the default values.

A column with the IDENTITY property allows only integer values, which are usually implicitly assigned by the system. Each value, which should be inserted in the column, is calculated by incrementing the last inserted value of the column. Therefore, the definition of a column with the IDENTITY property contains (implicitly or explicitly) an initial value and an increment. This property will be discussed in detail in the next chapter (see Example 6.42).

To close this section, Example 5.5 shows the creation of a table with a column of the SQL_VARIANT type.

USE sample;
CREATE TABLE Item_Attributes (
		item_id INT NOT NULL,
		attribute NVARCHAR(30) NOT NULL,
		value SQL_VARIANT NOT NULL,
		PRIMARY KEY (item_id, attribute) )Code language: PHP (php)

In Example 5.5, the table contains the value column, which is of type SQL_VARIANT. As you already know from Chapter “SQL components”, the SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. Note that in Example 5.5 the SQL_VARIANT data type is used for the column values, because different attribute values may be of different data types. For example, the size attribute stores an integer attribute value, and the name attribute stores a character string attribute value.