Managing Database Tables

To create a table you use the CREATE TABLE command (see CREATE TABLE). In this command you specify the table and columns to describe the data being stored in the table. For each column defined a data type must be specified. See SQL Data Types for all the NuoDB supported data types. The data type constrains the set of possible values that can be assigned to a column and assigns semantics to the data stored in the column so that it can be used for computations. For instance, a column declared to be of a numerical type will not accept arbitrary text strings and the data stored in such a column can be used for mathematical computations. By contrast, a column declared to be of a character string type will accept almost any kind of data but it does not lend itself well to mathematical calculations, although other operations such as string concatenation are available.

If you want to alter a table after it has been created, you can use the ALTER TABLE command (see ALTER TABLE ).

If you no longer need a table, you can remove it using the DROP TABLE command (see DROP TABLE).

Default Values

Columns can be assigned default values. When a new row is created and no values are specified for some of the columns, those columns wil be filled with their respective default values. If no default value is declared explicitly, the default value is NULL.

Constraints

A column check constraint allows you to specify that the value in a certain column satisfies a boolean expression. Check constraint expressions can also refer to several columns and would be defined as a table check constraint. A Not Null column constraint simply specify that the column must not assume the null value.

Unique Constraints ensure that the data constrained in a column or a group of columns is unique with respect to all the rows in the table. Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. Null values are not considered unique

A Primary key constraint is simply a combination of a unique constraint and a not null constraint. Adding a primary key will automatically create a unique btree index on the column or group of columns used in the primary key. There can be any number of unique and not null constraints defined for a table, but only one can be identified as a primary key. A table can have only one primary key.

NuoDB allows the definition of foreign key constraints. A table cannot be dropped if it is referenced in the definition of another table.

Columns can be defined with more than one constraint. If you have several columns across several tables that you want to define with the same data type, size, default value, constraint, etc. you can use the CREATE DOMAIN command to create a user defined data type (see CREATE DOMAIN).