Creating the Job Table

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 datatype for storing descriptive text. The text following the code discusses these new elements.

1. 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 datatype used for the job_requirement column is a dynamically sizable datatype 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.

Advance To:

Primary Keys and Foreign Keys