InterBase Quick Start: Part II - Creating Tables

From InterBase
Jump to: navigation, search

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.

Image 025.jpgCreating the Country Table

The first 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 and in addition is NOT NULL and a primary key. Primary keys are discussed in a following section of this tutorial.

1. If you are not already connected, in IBConsole, connect to tutorial.gdb as TUTOR.
2. In the Interactive SQL dialog enter the following CREATE TABLE statement. The layout below is for ease of reading; the line endings are not required:
CREATE TABLE Country (
     country COUNTRYNAME NOT NULL PRIMARY KEY,
     currency VARCHAR(10) NOT NULL)
  • Notice that the collection of column definitions is surrounded by parentheses, and that the columns are separated by commas.
3. Execute the statement click (ExecuteButton.png), press F5 or choose Query > Execute). If you entered the code without errors, the tables now exist in the database in IBConsole. Choose Tables from the left pane.


IBConsole Country table.png

Image 025.jpgCreating the Department Table

Next, you create the Department table. This table has only two columns to begin with. Later, you use the ALTER TABLE command to add to it. Type in the complete SQL statement and then execute it:

 
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. Primary keys are discussed in PRIMARY KEY and UNIQUE Constraints. Notice that the department column value must be unique and that neither column can be null.

Image 025.jpgCreating the Job Table

Now 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. The text following the code discusses these new elements. Enter the following CREATE TABLE statement in the Interactive SQL statement area. Type in the whole statement and then execute it.

 
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 at the end checks 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 used for the <job_requirement> column is a dynamically sizable data type that has no specified size and encoding. It is used to store large amounts of data such as text, images, sounds, and other multimedia content.

2. To check that the tables now exist in the database, in IBConsole, choose Tables from the left pane.

IBConsoleTables.png

Advance To: