Creating a Table with SQL Constraints

BearDB supports the SQL constraints of NULL column specifiers, Unique, Primary Keys, and Foreign Keys.

By default when using the CREATE TABLE syntax, columns are created that allow NULL values. When creating a table, one should consider whether the data in each column could/should be null.
BearDB allows the specification of a table column as not allowing null values when the NOT NULL option is used with CREATE table:

   columnOne  varchar(32) NOT NULL,
   columnTwo  varchar(24),
   columnThree  integer NOT NULL,
   columnFour  char(6)

This CREATE TABLE query will make a table with 4 columns where columnOne and columnThree do not allow NULL values.
Note that since a column can allow NULL values by default, the CREATE TABLE query should only contain the not null option when a column should not allow nulls.
The SQL parser will ignore the null keyword by itself.

The CREATE TABLE syntax in BearDB does not support the concept of a default value at this time.

Tables can be created with PRIMARY KEY or FOREIGN KEY columns. These column types are defined as follows in BearDB:

  • PRIMARY KEY: A column specified as the primary key can only have unique values, and may not be null. BearDB allows multiple PRIMARY KEY columns in a table.
  • FOREIGN KEY: A column specified as a foreign key has a reference to a column in another table which is defined as a primary key. A FOREIGN KEY column must reference a PRIMARY KEY column from another table.

Use the CREATE TABLE syntax with a PRIMARY KEY or FOREIGN KEY to enforce uniqueness:

   idColumn  autonumber PRIMARY KEY,
   firstName  varchar(24),
   middleName  integer NOT NULL,
   lastName  char(6)

CREATE TABLE myAddresses
   idColumn  autonumber PRIMARY KEY,
   nameidColumn  int FOREIGN KEY REFERENCES myNames.idColumn,
   address1  varchar(35) NOT NULL,
   address2  varchar(35),
   city  varchar(35)
   state  varchar(30)
   zip  char(5)

In the above CREATE TABLE statements there is a FOREIGN KEY from the myAddresses table back to the idColumn column in the myNames table. This FOREIGN KEY will put the following rule into the myAddresses table:

    Values inserted into myAddresses.nameidColumn must be unique, must already exist in myNames.idColumn, and must not be null.