InterBase Quick Start: Part II - Creating Tables

Go Up to InterBase Quick Start: Part II - Data Definition

A table is a data structure consisting of an unordered set of rows, each containing a specific number of columns. Conceptually, a database table is like an ordinary table. Much of the power of relational databases comes from defining the relations among the tables. The CREATE TABLE statement has the following general form:

CREATE TABLE tablename 
  (
    colname1 characteristics[,
    colname2 characteristics, …]
    [, tableconstraint …]
  )

• Characteristics must include a data type and can also include several other things. See InterBase Quick Start: Part II - Creating Domains for a list of column characteristics.
• A tableconstraint can be a CHECK, UNIQUE, FOREIGN KEY, or PRIMARY KEY constraint on one or more columns.

For the full syntax of the CREATE TABLE statement, see the Language Reference Guide.

Creating the Country Table

The Country table has two columns. The column definitions are separated by commas. For each column, the first word is the column name and the following words are characteristics. The first column, country, has the COUNTRYNAME domain, is NOT NULL and is a primary key (primary keys are discussed in a following section of this tutorial).

1. Open the Interactive SQL window and enter the following statement:

   CREATE TABLE Country
     (
        country  COUNTRYNAME NOT NULL PRIMARY KEY,
        currency VARCHAR(10) NOT NULL
     )
   

   You can see that the set of column definitions is surrounded by parentheses, and that the columns are separated by commas.

2. Execute the statement.
3. If you enter the code without errors, the table now exists in the database. To confirm, close the Interactive SQL window and select Tables in the left pane of the IBConsole. The image below show the expected result.

   

Creating the Department Table

Next, you create the Department table. This table only has two columns to begin with. Later in this tutorial, you use the ALTER TABLE operation to add more columns to it.

1. Enter and execute the following statement:

CREATE TABLE Department
  (
     dept_no    DEPTNO NOT NULL PRIMARY KEY,
     department VARCHAR(25) NOT NULL UNIQUE
  )

The dept_no column is the primary key for the table and is therefore UNIQUE. The department column value is also UNIQUE. Neither of the above can be NULL.

Note:
For more information about primary keys, see PRIMARY KEY and UNIQUE Constraints.

Creating the Job Table

In this step you create the more complex Job table. This definition includes CHECK constraints, PRIMARY KEY and FOREIGN KEY constraints and a BLOB data type for storing descriptive text. You can find detailed explanation about these elements below the code snippet.

1. Enter and execute the following statement:

   CREATE TABLE Job
     (
        job_code         JOBCODE NOT NULL,
        job_grade        JOBGRADE NOT NULL,
        job_country      COUNTRYNAME NOT NULL,
        job_title        VARCHAR(25) NOT NULL,
        min_salary       SALARY NOT NULL,
        max_salary       SALARY NOT NULL,
        job_requirement  BLOB SUB_TYPE TEXT SEGMENT SIZE 400,
        language_req     VARCHAR(15)[1:5],
        CONSTRAINT pkjob PRIMARY KEY (job_code, job_grade, job_country),
        CONSTRAINT fkjob FOREIGN KEY (job_country) REFERENCES Country (country),
        CHECK (min_salary < max_salary)
     )
   

   • The CHECK constraint ensures that the minimum salary is less than the maximum salary.
   • The three-column primary key guarantees that the combination of the three columns identifies a unique row in the table.
   • The foreign key checks that any country listed in the Job table also exists in the Country table.
   • The BLOB data type of the <job_requirement> column is a dynamically sizable data type that has no specified size and encoding. It is suitable for storing large amounts of data such as text, images, sounds, and other multimedia content.
2. To check that the tables now exist in the database, close the Interactive SQL window and select Tables in the left pane of the IBConsole. The image below show the expected result.

   

Advance To:

• Primary Keys and Foreign Keys