R:BASE 11 Beginners Tutorial

When we designed the database in Lesson One, we decided that the EmployeeID and CustomerID columns would be key columns. They are the columns that enforce the relationship constraints between the tables. When flights are entered, we want to make sure that the EmployeeID and CustomerID values exist in the Employee and Customer tables. We use R:BASE's built-in constraints to enforce the relationships between our tables. In addition to enforcing the relationships between tables, by defining constraints, we automatically create indexes on the columns to speed data retrieval. There can be multiple primary keys in one table as well as multiple foreign keys in a table.

 

The Employee table has a one-to-many relationship with the Flights table, using the column EmployeeID, as there can be only one unique employee in the Employee table, and there can be many flights entered in the Flights table by an employee. The EmployeeID column would be the primary key for the Employee table and EmployeeID would be the foreign key in the Flights table.

 

The Customer table also has a one-to-many relationship with the Flights table, using the column CustomerID, as there can be only one unique customer in the Customer table, and there can be many flights entered in the Flights table for a customer. The CustomerID column would be the primary key for the Customer table and CustomerID would be the foreign key in the Flights table.

 

First, we'll make the EmployeeID column in Employee a primary key. A primary key is used to identify the columns that uniquely identify a row in a table. A primary key must be a unique value. The difference between a primary key and a foreign key is that the primary key data must be unique to that table and the foreign key can occur more than once.

 

To define a primary key:

 

 

R:BASE displays currently defined indexes and constraints for the selected table. The window is blank, as none have been defined.

 

 

R:BASE displays a sorting menu. The columns in the Employee table are listed. Sometimes, tables have multiple column keys. Our primary key is a single column key.

 

 

 

You must choose the sort order for the constraint index.

 

 

 

The "New Key/Index" dialog box is displayed.

 

 

 

The dialog box automatically displays options applicable to a primary key. We won't edit the default messages.

 

 

The primary key you just defined is now listed as a key/index for the Employee table.

 

 

Now, repeat this procedure to make the CustomerID column in the Customer table a primary key.

 

Then, repeat this procedure again to make the FlightID column in the Flight table a primary key.

 

Next, we'll define the foreign keys for the Flights table. When we defined the table relationships, we noted that we could have many rows for each employee and many rows for each customer in the Flights table. By defining these columns as foreign keys, we ensure that matching values exist in the Employee and Customer tables.

 

To define a foreign key:

 

 

R:BASE displays currently defined indexes and constraints for the selected table.

 

 

R:BASE displays a sorting menu listing the columns in the Flights table. Sometimes, tables have multiple column keys. Our foreign keys are single column keys to match the defined primary keys.

 

 

The "New Key/Index" dialog box is displayed.

 

 

The dialog box automatically displays options applicable to a foreign key.

 

R:BASE requires you to select a "Referenced Table," the table with the corresponding primary key for this foreign key.

 

 

 

Normally, there is only one number to choose in the "Referenced Key:" drop down box, unless more than one primary key is defined for a table.

 

 

The foreign key you just defined is now listed as a key/index for the Flights table.

 

Now make the CustomerID column in the Flights table a foreign key. Repeat the steps above, and select "Customer" as the matching (referenced) primary key table.