Lab 3: Constraints (Summer, 2012)

Instructions for Lab Requirements begin on page 9


Q1: How do I get to the i-series Navigator?

Ans: Click on the i-series icon on the desktop.

Click on System I Navigator.

Click on the + sign beside

Q2: How do I get to the “green screen” from the Navigator?

Ans: Right-click on

Click on Display Emulator.

Q3: How do I get to the “Enter SQL Statements” screen from the “System I Main Menu” of the “green screen”?

Ans: On the command line type STRSQL and press <Enter>.

Q4: I get an error message that I don’t understand, what should I do?

Ans: Place the cursor on the message and press <F1>. The system will give more information about the error.

Q5: ADDLIBLE à Why do I have to re-add my schema every time I start a new session on the “Green Screen”?

Ans: This happens because the list is cleared and reset to a default list at the start of a new session.



To add a collection to your collection list on the “green screen”

Start the terminal emulator (“green screen”) and go to SQL (STRSQL).

Using the green screen you will not have access to your PREKxxx schema. You must add the schema each time you start a new session on the green screen using the ADDLIBLE command.

Exit the SQL screen by pressing F3

Type the command ADDLIBLE PRExxxx and Press ENTER.

You should receive a message stating the collection list has been changed.
- go back into SQL by entering the command STRSQL.

Note: You must add your schema every time you start a new session on the “green screen.

The implementation of a database covers three steps to be completed in the following order:

1.  Create the collection (schema) and create the tables (see lab 2)

2.  Add the constraints

a)  primary keys (another way to deal with primary keys is to add them AFTER a table has been created and provides the database with entity integrity)

b)  foreign key(s) (which establishes a 1:M relationship between two tables and provides the database with referential integrity)

c)  check constraint (which imposes conditions upon the data that can be entered in a specifc column

d)  unique constraint (a condition on a column of data that does not allow data to be duplicated.)

e)  Not null with default

3.  Add the data to the tables



1.  How To Establish 1:M relationships between tables using Foreign Keys.

2.  The Syntax of the command to identify the Foreign Key.

3.  How the database preserves referential integrity using Foreign Keys.


1.  How to Add a CHECK constraint


1.  How to Add a UNIQUE constraint

D.  Adding data to tables and what it means if the data is not accepted.

A.1. 1:M relationships between tables using foreign keys:

è Definition: A Foreign Key is a field on one table that is the Primary Key of another table.

- The relationship between SALESREP and CUSTOMER is 1:M.

- An examination of the data in the customer table (Premiere database) shows that a particular salesrep can have many customers but,

- each particular customer has one and only one salesrep.

The Foreign Key field is always placed on the "many" table when there is a 1-to-many relationship between 2 tables..

A2) Syntax of the command that sets up the Foreign Key (after the table has already been created)

For example, a Family Doctor has many Patients but a particular patient has one-and-only-one Family Doctor. The PATIENT table will have the Foreign Key.

Here is the example:



FOREIGN KEY (Doctor_Number)


Note that in DB2, every constraint must have a name (as in prexxxx.doctor_fk)

Using the above example as your guide, write theSQL script that create s the Foreign Key to relate the CUSTOMER and SALESREP tables. (just keep in mind the idea that the item that you are defining as the foreign key refers to the primary key of another table)

Alter table PRExxxx.Customer

Add constraint prexxxx.customer_FK

Foreign key (sales_rep_number)

References prexxxx.salesrep(sales_rep_number)

Press Enter and read the message above the command line to verify that the constraint has been created.

A3) How the DBMS preserves referential integrity using Foreign Keys.

è Definition: Referential Integrity: When we want to add a new Customer, we must specify a Salesrep_Number of a Salesrep that already exists on the SALESREP table.

We cannot “refer” to a Salesrep that does not exist!

Let’s try to add a new Customer. The database should refuse to add the new row.


Customer Number / Last Name / First Name / Street / City / State / Zip Code / Balance / Credit Limit / Sales Rep Number /
888 / Anapurna / Raj / 201 Pond St / Toronto / ON / 11111 / 2000 / 3000 / 99 /

Remember that text and character data are enclosed by single quotes; interger and decimal data are not.

The script is written as follows:



(‘888’, ‘Anapurna’, ‘Raj’, ‘201 Pond Street’, ‘Toronto’, ‘ON’, 11111, 2000, 3000. ‘99’)

Look at the data for the new salesrep:


Sales Rep Number / Last Name / First Name / Street / City / State / Zip Code / Commission / Rate /
99 / Langer / Libby / 1York
St / Toronto / ON / 11111 / 4444 / 0.03 /

It looks like we must enter Salesrep number ‘99’ to the SALESREP table before we can add Customer ‘888’ to the Customer table. Because of the Foreign Key constraint, the database will not allow us to add a Customer who references a Salesrep who does not exist on the SALESREP table.

If we add Salesrep ‘99’ to the SALESREP table, THEN the INSERT into CUSTOMER command that failed before should work

Insert the record for Sales Rep 99 into the table:

Insert into PRExxxx.salesrep


(‘99’, ‘Langer’, ‘Libby’, ‘1York St’, ‘Toronto’, ‘ON’, 11111, 4444, 0.03)

Now we’ll try the INSERT INTO CUSTOMER that failed before.
Retrieve the statement you had used to try to insert a row in the CUSTOMER table (use F9). Press Enter to run the statement. You should not get an error this time and a row should have been added to your CUSTOMER table.

As you can see the Foreign Key Constraint performs 2 functions:

i. The Foreign Key links the two tables together

ii. The Foreign Key preserves referential integrity between the 2 tables.

B1) Adding a CHECK constraint.

A check constraint establishes a rule about values that are allowed in a particular field. For example, we could establish a check constraint that allows only the values ‘M’ or ‘F’ for a gender column.
Let's add a check constraint to the SALESREP table. We could have done this when we created the table but because the table already exists we will use the ALTER TABLE command.

Example 1:Here is the syntax for a typical CHECK constraint – a range check:



CHECK (Salary Between 20000 and 40000)

If the user attempts to change his friend’s salary to 50000, the database will refuse to make the change because it is not in the range of acceptable values. (Note: 20000 and 40000 would be accepted.)

Example 2: Another type of check constraint ensures that the field value is in a list.



CHECK (Grade IN (‘A’, ‘B’, ‘C’, ‘D’, ‘F’))

In this case if the user enters a Grade of ‘E’ , the database will refuse to make the change because it is not in the list of acceptable values.

.Let’s try one constraint based on a LIST :

Field Name: Rate

Table Name: SALESREP

Constraint Type: CHECK

Constraint Name: Rate_CK

Constraint Condition: RATE must equal 0.05 or 0.06 or 0.07 or 0.15

Remember: The IN keyword is used for a list of values. Write the constraint here:



Make sure that you receive the completion message "Alter completed for table SALESREP in PREKA12".

Let’s try a constraint based on a RANGE:
Field Name: Balance

Table Name: CUSTOMER

Constraint Type: CHECK

Constraint Name: Balance_CK

Constraint Condition: Balance must be greater than 0 and less than 2001

Hint: We could use (BALANCE > 0 AND BALANCE < 2001)

We could also use the BETWEEN 1 AND 2000

Write the constraint here


C1. Adding a UNIQUE constraint

A UNIQUE constraint on a column permits a value to occur only once in that column in the entire table.


Assume that a city can have only one sales rep assigned. The values in the city column in the SALESREP table must be therefore be unique. We must prevent the user from assigning more than one Salesrep to any particular city.

Field Name: City

Table Name: SALESREP

Constraint Type: UNIQUE

Constraint Name: City_UN

Constraint Condition: A city can be assigned to one and only one Salesrep




Once again, make sure you receive the "Alter completed" message.

Let’s write the UNIQUE constraint for an imaginary EMPLOYEE table:
Field Name: Ontario_Medicare_Num

Table Name: EMPLOYEE

Constraint Type: UNIQUE

Constraint Name: Ontario_Medicare_Num_UN

Constraint Condition: Any particular Medicare Number can occur only once on the table.



Required in this Lab:

1.  Sign in to the system ‘green screen’ using the Emulator

2.  Add your PRExxxx collection

3.  Confirm that you have a Customer table, and a Salesrep table.

4.  Add the following constraints:

a)  Foreign key – the constraint showing referential integrity between the customer and sales rep tables

b)  Check constraint – the customer credit limit must fall between $500 and $2000

c)  Check constraint –a salesrep may earn a 3%, 5% or 7& commission only

d)  Unique constraint -Salesreps – no two reps can have the same first name.

5.  Insert the data from pages 3,4 of this lab first into the customer table, and then into the salesrep table.

6.  If the data is NOT accepted into the customer table, try inserting data into the salesrep table and THEN into the customer table. (i.e., data must go into the parent table before it will be accepted by the child table, and you can’t enter data for a rep that doesn’t exist)

7.  Display your tables (use the select statement)

8.  Display your constraints (use the select statement)

a). You can view your list of CHECK constraints using the GUI interface by double clicking on the SYSCHKCST table. Try it now. On the green screen it is:

SELECT Check_Clause


b)You can view your list of KEY and UNIQUE constraints using:



9.  Return to the page that lists ‘system attribites (Shift_F1) and change the display attribute to PRINT

10.  Run your select statements again for:

a)  The customer table

b)  The salesrep table

c)  The check constraints

d)  The key constraints

11.  Print these 4 files and hand in .

Each lab should include student name and lab number


SQL Then Design: Lab03zz.doc / Design Then SQL: Lab08zz.doc / No Prompting: Lab03NP.doc

Page 1 of 8