InterBase Quick Start: Part II - Creating Tables with a Script

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

You should now understand how to create tables. You create the rest of the tables using an SQL script. If you wish to examine the syntax regarding tables further, take a look at the TABLES.SQL script. For example, the definition of the Employee table contains a complex CHECK constraint for the salary column:

CHECK ( salary >=
(
       SELECT min_salary
       FROM   job
       WHERE  job.job_code = employee.job_code
       AND    job.job_grade = employee.job_grade
       AND    job.job_country = employee.job_country)
AND
salary <=
(
       SELECT max_salary
       FROM   job
       WHERE  job.job_code = employee.job_code
       AND    job.job_grade = employee.job_grade
       AND    job.job_country = employee.job_country))

This constraint ensures that the salary of an employee is greater than the minimum salary for the job of that employee (specified by job_code, job_grade, and job_country) and lesser than a corresponding maximum salary.

Run the TABLES.SQL Script

Load the TABLES.SQL script.
Execute the query.
Note:
If you see any errors, refer to the Troubleshooting section for help.
To confirm that the tables now exist, click on Tables item in the left pane of IBConsole. You should see the following tables:

Time to Back up

If you have successfully executed the TABLES.SQL script, it is a good time to back up your database.

Troubleshooting

If you make a mistake when entering domain definitions, you get an error message when you run the TABLES.SQL script or when you define the tables manually.

To identify an error:

When you run a script: Take a look at the log file. You can find the log file in the same directory as your script file.
When you execute a query: The SQL output area displays any errors that occur.

In any case, if there is a problem with a particular table, an error message like this appears:

Statement failed, SQLCODE = -607 
Dynamic SQL Error
-SQL error code = -607
-Invalid command
-Specified domain or source column does not exist

Follow these steps to resolve the problem:

Read the error text. In this case it says that the specified domain does not exist. That means you probably made an error when typing the domain name.
Select Domains in the left pane of IBConsole.
You should see four domains that you manually defined: FIRSTNAME, LASTNAME, EMPNO, and DEPTNO. Confirm that the names of the domains are correct. It is likely that you misspelled a name.
Drop the incorrect domain by entering and executing the following command:
DROP DOMAIN domain_name
Recreate the domain using the appropriate CREATE DOMAIN statement.
Run the TABLES.SQL script again.

If you still see an error:

Identify the table related to the error.
Make sure that any domains that the definition for this table contains, exist in your database. If they do, the problem may be in the definition of a domain. To examine the definition of a specific domain, do the following:
1. Select Domains in the left pane of IBConsole.
2. Right-click on a domain name in the right pane and select Properties.
3. Switch to the Metadata tab.
4. Confirm that the domain definition is correct. Do this for any domains or tables that the error refers to.

As an example, this is the Metadata tab for the COUNTRYNAME domain:

Altering Tables

Take a look at the definition of the Department table.

Select Tables in the left pane of IBConsole.
Right-click on the Department table in the right pane and select Properties.
Switch to the Metadata tab. The image below shows what you should see:

You can change the structure of existing tables with the ALTER TABLE statement. A simplified syntax for altering a table is:

ALTER TABLE table_name 
  <operation1> [, 
  <operation2>, …]

operation = ADD column 
| ADD table_constraint 
| ALTER [column] col_name <alt_col_clause>
| DROP column
| DROP CONSTRAINT constraint_name

Notice how you can only drop a constraint if you assign it a name upon creation. For more detailed definition of the ALTER TABLE statement, see ALTER TABLE.

Altering the Department Table

This section guides you to add five new columns and two foreign key constraints to the Department table.

Open the Interactive SQL window and enter the following statement:

ALTER TABLE Department
  ADD head_dept DEPTNO,
  ADD mngr_no EMPNO,
  ADD budget BUDGET,
  ADD location VARCHAR(15),
  ADD phone_no PHONENUMBER DEFAULT '555-1234',
  ADD FOREIGN KEY (mngr_no) REFERENCES employee (emp_no) ON DELETE CASCADE ON
  UPDATE CASCADE,
  ADD CONSTRAINT fkdept FOREIGN KEY (head_dept) REFERENCES department (dept_no)
  ON DELETE CASCADE ON UPDATE CASCADE

Open the Metadata for the Department table again (see steps in Altering Tables section) and see the differences in the definition:

More Troubleshooting

If you receive error messages when you are alter tables or insert data, use the Metadata tab to investigate.

Examine the definition of each table that you enter by hand and compare it to the code snippets that this tutorial provides.
When you find a problem you can either drop the table and recreate it or use the ALTER TABLE statement to drop a column (ALTER TABLE table_name DROP column_name) and then add the column again using the correct definition (ALTER TABLE table_name ADD column_name column_def). If you misspell the name of the table itself, you must drop the table(DROP TABLE table_name) and recreate it.

Advance To:

Creating Tablespaces