Learning Oracle Database Programming

From: xmy <xmy009_at_163.net>
Date: 1 Dec 2001 22:13:09 -0800
Message-ID: <440264b2.0112012213.17d9ba99_at_posting.google.com>

Chapter 6: Referential Integrity

The next table you'll create is the account table. Each client has one or more accounts. Each

account has a unique 6 character account id which must be in the format nn-nnn ( n stands for

number). Like the client code, this may be expanded in the future. Once again you'll use a

system-assigned number as the primary key.

Defining Integrity Relationships

Each account must belong to one client: an account can't exist without a client. Before an

account is set up, its client must be in the client table. Therefore, the client_no, which is

the primary key of the client table, will be repeated in the account table. The client_no

column in the account table references the client_no in the client_table. This is referential

integrity. It helps protect the database from invalid data. The client table is considered the

parent and the account table is its child. Referential integrity constraints are placed on the

child, not the parent.

When you create a referential integrity constraint, you must decide what action should be taken

when the parent is deleted. The choices are

delete the child or
prevent the parent's deletion as long as a child exists.

Orphaned children aren't allowed. If you want to permit orphans, you can't use a referential

integrity constraint to support this.

Creating a Referential Integrity Constraint

For the account table, you'll tell Oracle to delete a client's accounts when the client is deleted. Create the account table by typing:

SQL> create table account (

2 accnt_no integer primary key,

3 accnt_id varchar2(6) not null unique,

4 client_no integer not null references

5 client(client_no) on delete cascade,

6 curr_balance number(11,2));

Table created.
The client_no column in the account table is a foreign key. A foreign key references a primary

key or a unique column in another table. The constraint could also be defined as "references

client" without explicitely stating the column because client_no is the primary key of the client

table.

Declaring client_no to be not null in addition to the references constraint is necessary because

references means the value must exist in the client.client_no column or be null. The business

requirements state each account must belong to a client. Null values are inappropriate here.

Check the constraints this create table statement created. Type:

SQL> select * from user_constraints

2 where table_name = 'ACCOUNT';

OWNER                          CONSTRAINT_NAME                C


------------------------------ ------------------------------ -

TABLE_NAME

SEARCH_CONDITION

R_OWNER                        R_CONSTRAINT_NAME

DELETE_RU
------------------------------ ------------------------------

STATUS

LOP                            SYS_C00377                     C

ACCOUNT
ACCNT_ID IS NOT NULL ENABLED

LOP                            SYS_C00378                     C

ACCOUNT
CLIENT_NO IS NOT NULL ENABLED

LOP                            SYS_C00379                     P

ACCOUNT ENABLED

LOP                            SYS_C00380                     U

ACCOUNT ENABLED

LOP                            SYS_C00381                     R

ACCOUNT

LOP                            SYS_C00369                     CASCADE

ENABLED Constraint SYS_C00381 is the foreign key constraint. It references constraint SYS_C00369.

Check that constraint. Type:

SQL> select * from user_constraints

2 where constraint_name = 'SYS_C00369';

OWNER                          CONSTRAINT_NAME                C


------------------------------ ------------------------------ -

TABLE_NAME

SEARCH_CONDITION

R_OWNER                        R_CONSTRAINT_NAME

DELETE_RU
------------------------------ ------------------------------

STATUS

LOP                            SYS_C00369                     P

CLIENT ENABLED Constraint SYS_C00369 is the client table's primary key constraint.

Next you'll modify the lop_util package body adding account.accnt_no to lop_util.get_next_seqno.

Edit file utilb.sql (changes are in bold):

Creating package body for lop_util

File Name: utilb.sql

Purpose: To create the lop_util package body

Changes: 7/20/96 F.E. Added account.accnt_no to if condition.

create or replace package body lop_util is

Use errors -20001 to -20029 when raising errors in this package.

function get_next_seqno(object_name in varchar2) return integer is

seqno integer;

err_msg varchar2(80);

begin

if (upper(object_name) = 'CLIENT.CLIENT_NO' or

upper(object_name) = 'ACCOUNT.ACCNT_NO') then

select lop_seq.nextval

into seqno

from dual;

return seqno;

else

raise_application_error(-20002,'There is no source for ' ||

object_name);

end if;

exception

when others then

err_msg := sqlerrm;

raise_application_error(-20001,

'Error raised in get_next_seqno. ' || err_msg);

end get_next_seqno;

end lop_util;

Replace the package body:

SQL> set document on

SQL> _at_c:\lop\utilb

DOC>Creating package body for lop_util

DOC>File Name: utilb.sql

DOC>Purpose: To create the lop_util package body

DOC>Changes: 7/20/96 F.E. Added account.accnt_no to if condition.

DOC>*/ Package body created.
Create a pre-insert for each row trigger similar to the one you created for the client table

to assign a number to the primary key, accnt_no. Type and then save or create a file,

acbri.sql (account before row insert), and then execute it:

SQL> create or replace trigger account_bri

2 before insert on account

3 for each row

4 begin

5 /* Get a sequence number for the primary key */

6 :new.accnt_no := lop_util.get_next_seqno('account.accnt_no');

7 end;

8 /

Trigger created.
SQL> save c:\lop\acbri

Created file c:\lop\acbri
Also create a pre-update for each row trigger to prevent the updating of account.accnt_no.

Type and then save (or create and then execute a file):

SQL> create or replace trigger account_bru

2 before update on account

3 for each row

4 begin

5 /* No updates allowed on primary key */

6 if (:new.accnt_no != :old.accnt_no) then

7 raise_application_error(-20000,

8 'Accnt_no is a primary key - not updatable.');

9 end if;

10 end;

11 /

Trigger created.
SQL> save c:\lop\acbru

Created file c:\lop\acbru
Before you test your triggers, you'll add several check constraints to the account table to

ensure an accnt_code is in the form nn-nnn. You'll also redefine the curr_balance column so

it will be given a default value of 0 if a record is inserted without any value for that column.

Type:

SQL> alter table account add(

2 check(length(accnt_id) = 6),

3 check(ltrim(substr(accnt_id,1,2),'1234567890') is null),

4 check(ltrim(substr(accnt_id,4),'1234567890') is null),

5 check(substr(accnt_id,3,1) = '-'));

Table altered.
Notice the various usages of the substr function. Substr(accnt_id,1,2) returns the first two

characters of the accnt_id. Substr(accnt_id,4) returns the characters in accnt_id from the

fourth character to the end of the string.

Next, use the modify form of the alter table command to add the default value option to the

column definition. This is not considered a constraint. (Remember, the only type of constraint

that can be created with the modify form of alter table is the not null constraint.) Type:

SQL> alter table account modify (curr_balance default 0);

Table altered.

Testing Constraints

Test your triggers and changes to lop_util by inserting some valid and some invalid records.

You'll need to know the valid values of client_no. Type:

SQL> select * from client;

CLIENT_NO CLIEN SHORT_NAME
--------- ----- ----------

LONG_NAME

1 12345 LASCO 2 03030 ROBSEC
Robin Securities

3 34534 INSTRES
The Institute for Research
Now insert some records. You only have to include data for accnt_id and client_no because

your trigger will assign a value to the mandatory accnt_ no column, and you told Oracle to

assign a default value to the curr_balance column. Your values for client_no may be different.

Type (you can leave out the comments):

SQL> insert into account(accnt_id,client_no)

2 values ('22-333',1) /* valid record */;

1 row created.
SQL> select * from account;

ACCNT_NO ACCNT_ CLIENT_NO CURR_BALANCE
--------- ------ --------- ------------

35 22-333 1 0
SQL> insert into account(accnt_id,client_no)

2 values('11-222',6) /* invalid client_no */;