Setting Up a Schema

   

Bug Tracker uses three different tables: Program , Revision , and Bugs . As illustrated in Figure 10.1, Program is the master table, and Revision and Bugs are detail tables. The relationship among the tables is that Program s may have Revision s or Bugs . A Revision must belong to a Program . A Bugs must belong to a Program and may belong to a Revision .

Figure 10.1. The tables used in Bug Tracker.

graphics/10fig01.jpg

Specifying these relationships in the creation of a database enables InterBase to enforce referential integrity on the server. Listing 10.1 shows the SQL used to create the Bugs table.

Listing 10.1 SQL Used to Create the Bugs Table
 /*Bugs Table*/  create table bugs (      bug_id          integer not null,      bug_name        varchar (80) not null,      bug_description varchar (255),      bug_resolved    smallint not null,      bug_date        date not null,      pro_id          integer not null,      r_id            integer,  primary key (bug_id),  constraint fk_bugs_pro_id foreign key (pro_id) references program (pro_id),  constraint fk_bugs_r_id foreign key (r_id) references revision (r_id));  

NOTE

At the end of each constraint declaration, the on update (cascadeset null) and on delete (cascadeset null) statements can be added. These are executed when the selected field in the table being referenced is modified or deleted and saves time by not requiring triggers to make the changes needed to maintain referential integrity.

For example, if we changed to

[View full width]
 
[View full width]
constraint fk_bugs_pro_id foreign key (pro_id) references program (pro_id) on update graphics/ccc.gif cascade on delete cascade,

when any Program table records were deleted, or when any of the Program tables pro_id values were changed, any Bugs record would be deleted or modified automatically.


We can define foreign keys without a name and have InterBase name them, but it is far easier to debug violation of FOREIGN KEY constraint "FK_BUGS_PRO_ID" on table "BUGS" than constraint "INTEG_XX" . Unfortunately, the table's primary key cannot be assigned a name.

NOTE

The database supplied with the CD-ROM accompanying this book, BCB5BOOK6.GDB , was designed under InterBase 6.0 and will not be accessible under any earlier InterBase version.


The Bugs table consists of fields that are used to describe the bug and foreign keys. Each field has a name, a type, its length, and an indicator if it must have a value assigned. If the field is identified as not null, InterBase will not allow a record to be posted with that field as null.

Although this is useful, it is the foreign keys that specify referential integrity for InterBase to enforce. The Bugs table has two foreign keys: one that identifies a relationship to the Program table, and one that specifies a relationship to the Revision table.

InterBase automatically enforces specified referential integrity. In our sample program, a Bug record can reference a Program record. An attempt to delete a Program record will return an error message such as Violation of FOREIGN KEY constraint "FK_BUGS_PRO_ID" on table "BUGS" . Additionally, an attempt to insert a record into either the Bugs or Revision table without a valid pro_id will return an error message.

As another example, we declared the r_id foreign key in the Revision table as permitted to hold a null value. Any other value assigned to this key must correspond to a valid revision record or a constraint error message will be returned.

Referential integrity rules protect a database from bad or invalid data sent by client applications. To take full advantage of this protection, it is important to invest time and care in designing and testing the database schema. After a database becomes live, it is extremely difficult to change any of its relationships without deleting data. In a recent application that was developed, one table's primary key had to be changed. The change would have taken a couple of minutes in the development stage, but, because the database was live, it took several hours of programming to create the large SQL script necessary to make the update without affecting any of the existing data.


   
Top


C++ Builder Developers Guide
C++Builder 5 Developers Guide
ISBN: 0672319721
EAN: 2147483647
Year: 2002
Pages: 253

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net