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
, orPRIMARY KEY
constraint on one or more columns.
For the full syntax of the CREATE TABLE
statement, see the Language Reference Guide.
Contents
Creating 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 (), 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.
Creating 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.
Creating 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
- 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.
- The
2. To check that the tables now exist in the database, in IBConsole, choose Tables from the left pane.