When you define a foreign key constraint you can specify all but one?

Cascading Updates and Deletes

Foreign key constraints currently enforce table relationships. They prevent data from changing when a DELETE or an UPDATE statement would change the relationship between tables. This idea has been extended to incorporate cascading updates and deletes as defined in the ANSI SQL standard. The default definition of a foreign key is equivalent to using the NO ACTION constraint as follows:

ALTER TABLE dbo.[Order Details] ADD

CONSTRAINT FK_Order_Details_Orders FOREIGN KEY

 (OrderID) REFERENCES Orders (OrderID)

 ON DELETE NO ACTION

 ON UPDATE NO ACTION

This indicates that if a delete or an update should affect the OrderID column in a row of the referenced Orders table, no action is to be taken. Therefore, the following statement will fail:

DELETE FROM Orders where OrderID=10250

We can change this foreign key definition to automatically delete rows in the Order Details table when the related order is deleted. First we must drop the constraint, then recreate it with the CASCADE option:

ALTER TABLE dbo.[Order Details]

 DROP CONSTRAINT FK_Order_Details_Orders

GO

ALTER TABLE dbo.[Order Details] ADD

CONSTRAINT FK_Order_Details_Orders FOREIGN KEY

 (OrderID) REFERENCES Orders (OrderID)

 ON DELETE CASCADE

 ON UPDATE CASCADE

Now, if we execute the DELETE statement again, the related rows in the Order Details table will be deleted as well.

Error Handling

Databases also differ in the error codes and messages they return when a database action results in an error condition, such as when an application tries to insert duplicate records in a table or when foreign key constraints are violated. When migrating databases to Oracle, it is essential that applications are modified to make sure they correctly return the error messages and codes returned from an Oracle database.

In some cases, users write code to take a special action on certain error conditions (e.g., update a table when the insert record action fails because the record exists in the table). In such cases, it is common to look for the duplicate record error message from the database and perform suitable operations, such as deleting the existing record in the row and inserting the new record, or simply updating the existing record in the database with the new values for all columns. Therefore, it is essential that the application programs have the correct error codes that are returned by an Oracle database for them to perform accurately. Table 7.7 describes common error codes that are returned by an Oracle database.

Table 7.7. Common Error Conditions and Codes Returned by Oracle, DB2, and SQL Server/Sybase

Databases return hundreds of error codes, but a majority of them are not handled within application programs because application programs typically do not perform many operations such as database maintenance (creation and deletion of objects), security grants, or space allocations in databases and tablespaces. It is always best to return the error codes and messages as returned by the database instead of processing them again in the application program. Doing so can help you to avoid hard-coding error codes and messages in the applications.

Unenforced Referential Integrity

In the preceding section, we discussed the problem of orphaned foreign keys. They represent a violation of referential integrity that occurs after the data have been stored and can be handled with strict foreign key definitions when the tables are created. However, what happens if foreign key constraints are never added to a table definition? Foreign keys may then be orphaned as soon as they are added to the database because there is nothing to ensure that they reference existing primary keys.

As you might expect, the solution to this problem is straightforward: Ensure that referential integrity constraints are present for all foreign keys. Make this a policy that everyone who has the right to create tables in the database must follow.

Constraints and Referential Integrity

DBAs also configure databases with constraints, which are machine-applied rules governing the values that can be placed in the data tables. Some constraints, such as UNIQUE or NOT-NULL, operate on a single record. Other constraints are checks on values between tables. A common example of a multi-table constraint is a foreign key constraint, which ensures that a record will not be loaded in a table if the values in the foreign key column(s) cannot be found in the primary key column(s) of the parent table.

DBMS-enforced constraints consume processing power and therefore can slow down warehouse data loads considerably. For this reason, many DW/BI teams do not employ DBMS-enforced constraints in their databases, relying instead on the ETL to ensure that child records are not inserted without corresponding parent records. When the records in a database completely agree with the mandatory primary/foreign key constraints specified by the data modeler, the tables are said to have referential integrity.

Solution 6: Retrieval Query Writers

Even with all the previous tactics, the team will still need to write many queries that cannot employ bridging or point-in-time tables. These queries will involve a good number of outer joins and correlated subqueries and thus be difficult to write and maintain. Fortunately, EDW developers do not have to resign themselves to manually writing such complex joins every time they need a new query. They can either acquire a query writer from the hyper normalization community or build one of their own.

Before such query writers can begin to assist, the EDW developers will need to declare the primary and foreign key constraints when creating the tables of the hyper normalized integration layer. Many teams do not plan to use such constraints to ensure referential integrity because they slow down data loads. However, a team can declare these constraints and leave them “unenforced” so that they still document how tables in the data warehouse should be linked together but do not incur data checks when records are inserted into the database. With primary and foreign key constraints declared, query-writing programs can use them to discover the relationships between tables and intelligently construct a query’s WHERE clause.

Consider again the pruned model shown in the lower right of Figure 14.23. The most dependent table is ATTRIB_MANUFACTURER, from which we need values that are stored in the Manufacturer_Name column. With primary and foreign key constraints declared in the database, a program introspecting the DBMS’s system catalog regarding ATTRIB_MANUFACTURER will see that it has a foreign key that resolves to the column Manufacturer_SID in the LINK_MANUFACTURER_PACKAGE. The program could then introspect this table in turn, discovering that it similarly shares a key of Package_SID with LINK_LINE_ITEM. The program could progress in this manner until it has discovered all the tables required to traverse from ATTRIB_MANUFACTURER at the bottom to both ATTRIB_CUSTOMER and ATTRIB_AGENT at the top. It could then generate a query that includes joins between the business keys and linking tables, employing outer joins and correlated subqueries as needed to retrieve records from the hyper normalized data store.

EDW team leaders who wish to employ one of the data vault standards can find several online communities that are sharing already programmed query writers. Typically, such borrowed utilities are close to what a given team will need, but they will require some polishing. Thus, EDW leaders opting for hyper normalization should include some time for tuning query writers into their project plans. Once a tuned query-writing utility is in place, the EDW team will be able to upload to it the data definition language of the team’s hyper normalized warehouse, which will include the primary and foreign key constraints declared in the team’s database. After the utility has parsed the tables and constraints, the developers may have to declare “roles” for tables that participate in more than one join path. At this point, they will be able to select desired elements from a list of columns in the database, push a button, and receive the text of a data query that will provide the requested fields.

Modifying Database Elements

With the exception of tables, database elements are largely unchangeable. When you want to modify them, you must delete them from the database and create them from scratch. In contrast, just about every characteristic of a table can be modified without deleting the table, using the ALTER TABLE statement.

Adding Columns

To add a new column to a table, use the ALTER TABLE statement with the following syntax:

For example, if Antique Opticals wanted to add a telephone number column to the producer table, they would use

To add more than one column at the same time, simply separate the clauses with commas:

Adding Table Constraints

You can add table constraints, such as foreign keys, at any time. To do so, include the new constraint in an ALTER TABLE statement:

Assume, for example, that Antique Opticals created a new table named states, and included in it all the two-character US state abbreviations. The company would then need to add a foreign key reference to that table from the customer, distributor, and producer tables:

When you add a foreign key constraint to a table, the DBMS verifies that all existing data in the table meet that constraint. If they do not, the ALTER TABLE statement will fail.

Modifying Columns

You can modify columns by changing any characteristic of the column, including the data type, size, and constraints.

Changing Column Definitions

To replace a complete column definition, use the MODIFY command with the current column name and the new column characteristics. For example, to change the customer number in Antique Opticals’ customer table from an integer to a character column, they would use

When you change the data type of a column, the DBMS will attempt to convert any existing values to the new data type. If the current values cannot be converted, then the table modification will not be performed. In general, most columns can be converted to character. However, conversions from a character data type to numbers, dates, and/or times require that existing data represent legal values in the new data type.

Given that the DBMS converts values whenever it can, changing a column data type may seem like a simple change, but it isn’t. In this particular example, the customer number is referenced by foreign keys and therefore the foreign key columns must be modified as well. You need to remove the foreign key constraints, change the foreign key columns, change the primary key column, and then add the foreign key constraints back to the tables containing the foreign keys. Omitting the changes to the foreign keys will make it impossible to add any rows to those foreign key tables because integer customer numbers will never match character customer numbers. Moral to the story: before changing column characteristics, consider the effect of those changes on other tables in the database.

Changing Default Values

To add or change a default value only (without changing the data type or size of the column), include the DEFAULT keyword:

Changing Null Status

To switch between allowing nulls and not allowing nulls, without changing any other characteristics, add NULL or NOT NULL as appropriate:

or

Changing Column Constraints

To modify a column constraint without changing any other column characteristics, include a CHECK clause:

Deleting Table Elements

You can also delete structural elements from a table as needed, without deleting the entire table:

■

To delete a column:

■

To delete a CHECK table constraint (a CHECK that has been applied to an entire table, rather than to a specific column):

■

To remove the UNIQUE constraint from one or more columns:

■

To remove a table’s primary key:

Although you can delete a table’s primary key, keep in mind that if you do not add a new one, you will not be able to modify any data in that table.

■

To delete a foreign key:

Renaming Table Elements

You can rename both tables and columns:

■

To rename a table, place the new table name after the RENAME keyword:

■

To rename a column, include both the old and new column names, separated by the keyword TO:

V.A. Relational DBMS

The basic elements of a relational database schema define tables, columns in those tables, and constraints on those columns. Constraints include primary keys (identifiers) and foreign keys (relationships). While there are numerous efficiency issues related to transforming a data model into such a database schema, a “quick and dirty” approach simply maps entities into tables, attributes into columns, identifiers into primary keys, and relationships into columns designated as foreign keys.

Fig. 7 shows such a relational schema in tabular form for the data model of Fig. 5. Figure 6 shows its definition in the standard relational language SQL. The data model has five entities, Customer, Salesperson, Order, Line Item, and Product. Five corresponding tables are defined in the relational schema. Similarly, columns are defined for each attribute in each entity. In a relational schema all interconnections among tables are represented by data values. Hence, columns must be created to represent relationships. For a one-to-many relationship, a column is created in the table representing the entity on the “many” side for each attribute (column) in the identifier (primary key) of the table representing the entity on the “one” side. These columns are termed “foreign keys.”

As illustrated in Figs. 6 and 7, the Customer table has an “extra” column, spno (salesperson number) to represent the one-to-many relationship between Salesperson and Customer. It is designated in a Foreign Key constraint to reference the Primary Key column, spno, in the Salesperson table. The spno column in the Customer table is constrained to be NOT NULL. This constraint specifies that each row of the Customer table must have a value for that column. The Foreign Key constraint specifies that the value of that column must appear in the spno column of some row in the Salesperson table. Together these implement the specified relationship between Customer and Salesperson in the data model having a minimum 1 and maximum 1 on the Customer side. Removing the NOT NULL constraint would implement a minimum 0 and maximum 1 relationship on the Customer side. The minimum 0 and maximum many (unlimited) on the Salesperson side is implicit in the Foreign Key representation. Enforcing a minimum other than 0 or a maximum other than unlimited on the Salesperson (many) side requires a procedurally implemented constraint.

The Line Item table similarly has two “extra” columns: invoice_no (invoice number), representing its many-to-one relationship with Invoice, and pno (product number) representing its many-to-one relationship with Product. Again each is designated in a Foreign Key constraint; however, while invoice_no is designated to be NOT NULL, pno is not. This implementation requires each Line Item to have a related Invoice, but does not require it to have a related Product. Hence this schema implements the specified relationships in the data model of Fig. 5.

A one-to-one relationship may be represented in the table corresponding to either entity in the relationship. When one role in a one-to-one relationship has a minimum of 0 and the other has a minimum of 1, the relationship is most commonly represented by a foreign key in the table corresponding to the entity on the minimum 0 side. Consider, for example, the data model in Fig. 3. The Managing relationship could be represented by a foreign key in either the Employee table or the Department table. Since it has a minimum of 0 on the Department side, it would most likely be represented by a foreign key column constrained to be NOT NULL in the table corresponding to that entity. If this relationship was represented in the table corresponding to the Employee table, the foreign key column would not be constrained to be NOT NULL and, in fact, would contain a NULL value in each row corresponding to an employee who did not manage a department, likely most of them.

Generalization and Aggregation

The transformation of a generalization abstraction can produce separate SQL tables for the generic or supertype entity and each of the subtypes (Figure 5.7 for the ER model and Figure 5.8 for UML). The table derived from the supertype entity contains the supertype entity key and all common attributes. Each table derived from subtype entities contains the supertype entity key and only the attributes that are specific to that subtype. Update integrity is maintained by requiring all insertions and deletions to occur in both the supertype table and relevant subtype table—that is, the foreign key constraint cascade must be used. If the update is to the primary key of the supertype table, then all subtype tables as well as the supertype table must be updated. An update to a nonkey attribute affects either the supertype or one subtype table, but not both. The transformation rules (and integrity rules) are the same for both the disjoint and overlapping subtype generalizations.

Another approach is to have a single table that includes all attributes from the supertype and subtypes (the whole hierarchy in one table) with nulls used when necessary. A third possibility is one table for each subtype, pushing down the common attributes into the specific subtypes. There are advantages and disadvantages to each of these three approaches. Several software tools are now supporting all three options (Fowler, 2003; Ambler, 2003).

Database practitioners often add a discriminator to the supertype when they implement generalization. The discriminator is an attribute that has a separate value for each subtype and indicates which subtype to use to get further information. This approach works up to a point. However, there are situations requiring multiple levels of supertypes and subtypes, where more than one discriminator may be required.

The transformation of an aggregation abstraction also produces a separate table for the supertype entity and each subtype entity. However, there are no common attributes and no integrity constraints to maintain. The main function of aggregation is to provide an abstraction to aid the view integration process. In UML, aggregation is a composition relationship, not a type relationship, which corresponds to a weak entity (Muller, 1999).

Referential Integrity

Referential integrity (RI) is a term used with relational databases to describe the integrity of the business relationships represented in the schema. It ensures that relationships between tables remain consistent. As it applies to a logical data model, RI encompasses the rules that determine the actions that are taken when parent or child rows are inserted, updated, or deleted in a database. None of these actions—inserting, updating or deleting—can violate the relationships that have been defined.

RI includes enforcing relationship cardinality rules (e.g., one-to-many, many-to-one, etc.) and the various types of relationship models (e.g., identifying, nonidentifying optional, nonidentifying mandatory, and many-to-many). There are two primary approaches to implementing RI:

Leverage database functionality, such as foreign key constraints and triggers. Using this approach, when data is inserted, updated, or deleted in the database, it will enforce referential integrity.

Develop data integration processes such as ETL code. With this approach, the data integration processes will examine the data that is being inserted, updated, or deleted; determine what the relationship rules are for processing; and then take the appropriate action. This is the best practice.

The first approach appears to be the most straightforward because it uses prebuilt database functionality without the need for any custom coding. If the relationships are properly implemented in the database, it is guaranteed that referential integrity will be enforced. There are two significant drawbacks to this approach, however:

First, database performance can be adversely affected when using this approach.

Second, and most importantly, database inserts, updates, and deletions can only occur if all the data related to the relationships is available at the same time. Although this condition is met in transaction processing, it is often not the case in BI applications where data is being gathered from many different source systems that have different update frequencies and operate under different business rules. This means that if you use the first method, many BI-related database updates might not be completed, which then results in significant amounts of data never being updated or made available for analysis. Under these conditions, it is best to choose the second approach of developing code.

For these reasons, transactional applications commonly enforce referential integrity by using the first method of relying on the database. In BI implementations, the best practice has been the second method of developing data integration code that can systematically process data under agreed-upon rules to enforce referential integrity. An emerging best practice has been to define database referential integrity and then to turn it off during loads if ETL-enforced RI is needed to load data.

Figure 8.13 shows a fact table for store sales—Tbl_Fact_Store_Sales. Four entities surround Tbl_Fact_Store_Sales with the following relationships:

One-to-many relationship between the entity table item Tbl_Dim_Item, which is the product being sold at the store.

One-to-many relationship between the entity of date Tbl_Dim_Date, which is when the product was sold at the store.

One-to-many relationship between the entity of customer Tbl_Dim_Customer, which is the customer who bought the product.

Within Tbl_Fact_Store_Sales are three foreign keys that are used to define the primary key of that particular entity. Any of those three entities (SK_Item_ID, SK_Date_ID, or SK_Customer_ID) can exist on their own. But you cannot have a store sale without selling a product to a customer at a particular point in time. That is how referential integrity is defined. The store sale cannot occur unless those three foreign keys are brought into the entity.

Additionally, the example shows the buyer (Tbl_Dim_Buyer), which is a one-to-many relationship, but is optional in this case. There could be a buyer who actually bought the product from another company, or the purchase could have been in another business transaction. So, this is an optional relationship that is not enforced through referential integrity.

Creating Tables

The population of a base table resides in the database (unlike result tables and views, which we'll look at shortly). Base tables must be created before they are populated. In SQL-92 onward, base tables (and other aspects of the database) are defined within the scope of a database schema that is created using a create schema statement. The schema may be deleted using a drop schema statement. Commercial SQLs often provide a different syntax for creating a new database (e.g., create database).

In the SQL standard, tables are created using a create table statement. In addition to this, most DBMSs provide a graphical interface for users to create tables by entering text and selecting options. The main part of a table definition is a list of columns, identifying for each column the column name, its data type, and whether nulls are prohibited in the column (i.e., the column is mandatory). Constraints may also be declared for some of the columns.

The SQL-89 syntax of the create table statement may be summarized as shown. Here literal denotes a numeric or string constant, col-list denotes a list of one or more column names separated by commas, and unique-col-list denotes a column list that has collectively been declared to be the primary key or to be unique. If a constraint applies to a single column it may be declared at the end of the column definition. Multicolumn constraints must be declared at the end of the table definition. Check clauses are restricted to conditions that involve only one row of the table.

SQL-92 extended this syntax in many ways. Constraints may be named by inserting constraint constraint-name at the start of their definition. This facilitates meaningful error messages and dropping of constraints. Check clause conditions may range over many rows or tables, and general constraints may be declared using create assertion (mainly for intertable constraints). Foreign key constraints may include full/partial match conditions and referential actions. Constraints may be declared immediate or deferred. Macros for constrained data types may be declared with a create domain statement. SQL:1999 went much further. For example, an of clause may be used to declare a table to be of a user-defined type, previously declared as a row type with a create type statement. Subtables may be defined using an under clause to reference the supertable.

Commercial SQL dialects typically support all the SQL-89 functionality, possibly with some different syntax. They tend to support only some of the syntax from more recent versions of the standard. For example, constraint names and referential actions are widely supported, but assertions and unrestricted check clauses are rarely supported. Most vendors also provide extra features and syntax of their own.

As an example, let's see how to declare the relational schema for our software-retailer database. The graphical version of this schema is reproduced in Figure 13.1, along with the schema declaration in SQL-92 syntax. You can adapt this where needed to your SQL dialect of choice, and add constraint names as you see fit.

Notice the four intertable subset constraints (the equality constraint shows two of these). Unless we add these intertable constraints later, using an alter table or create assertion statement, the order in which we create the tables is important. If you wish to minimize the need for these later additions, you should wherever possible create the referenced table before the referring table. With our example, this entails creating the Customer table before the Invoice table and the Item table before the LineItem table.

Columns are optional by default, but may be declared mandatory using not null. SQL requires primary key columns to be declared not null. A unique constraint is assumed for primary keys, but may be explicitly declared for other columns or column lists, with the meaning that nonnull values are unique. For example, if the optional phoneNr column had instead been declared unique, an actual phone number may appear at most once in that column, but there may be many nulls in that column. Some commercial systems may consider null to be a value under these circumstances, in which case there could be at most one null value in the phoneNr column.

Foreign keys are declared with a references clause, indicating the referenced table, optionally followed by the referenced column list, which must already be declared to be the primary key or unique. If no referenced column list is given, the primary key is assumed to be the target.

The equality constraint in the graphical version of the software-retailer schema is equivalent to two subset constraints in either direction between LineItem.invoiceNr and Invoice.invoiceNr—a referential cycle. The subset constraint from LineItem.invoiceNr to Invoice.invoiceNr is a foreign key constraint, since the target is a primary key. So this may be declared using a references clause as shown. However, the subset constraint from Invoice.invoiceNr to LineItem.invoiceNr is not a foreign key constraint, since its target is just one part of a composite primary key. In SQL-92 onward this constraint may be declared using an assertion, as shown. In SQL dialects that do not support assertions (such as SQL Server), assertions can be coded instead using triggers or stored procedures. We'll look at referential integrity in more detail shortly.

A check clause specifies a condition to be satisfied. Value constraints are often implemented using check constraints. A check constraint is violated if and only if its condition evaluates to false (not true and not unknown). Some people find this unintuitive when compared with the where clause in a select statement, which must evaluate to true (not false and not unknown) in order for a row to be included in the result set.

Default options may be declared with a default clause. If a user inserts a row without specifying a value for a column, then the system automatically inserts the default value defined for that column. In SQL-89, the only allowed defaults were a literal value, user or null. In the absence of a default clause for an optional column, null is assumed to be the default. SQL-92 expanded the possible defaults to include the nullary functions: current_user, session_user, system_user, current_date, current_time, current_timestamp. SQL:1999 added current_role, current_path and any implicitly typed value specification (e.g., to deal with collection types).

Here is a suggested procedure for deciding where to declare constraints in SQL that you may find useful if you want to minimize keystrokes:

SQL:2003 added another column option to deal with the creation of identity columns, for which the column syntax is

There can be at most one identity column in a table and its data type must be numeric with a scale of zero (e.g., integer). When a row is inserted into the table, the system generates the next value in sequence, as defined by the rather complicated set of options. If always is specified, it is an error to try to insert a value into the identity column. If by default is specified, the system will generate a value if none is supplied, otherwise it will use the supplied value. If a row is subsequently deleted, its identity value is not reused.

The default value for both start with and increment by is 1, unless explicitly specified otherwise. The increment value can be positive or negative. The maximum and minimum values possible for a sequence are implementation dependent, and these will be adopted unless a specific maxvalue or minvalue is defined. The sequence stops and no more values will be generated once maxvalue (counting up) or minvalue (counting down) is reached or exceeded. Depending on the relationship between the start value and increment size, the sequence may or may not land exactly on a maxvalue or minvalue. A sequence will not cycle back and duplicate values already generated unless the cycle option is specified. If cycle is specified the sequence will stop at maxvalue and reset to minvalue if counting up, and the converse if counting down. The start value can be anywhere in the range defined by these two values.

Identity columns are supported in most implementations, although not necessarily with the standard syntax. The guarantee that the generated value will be unique within a table makes them attractive to use as a primary key. In cases where the natural primary key for the table is clumsy, for example, a combination of multiple varchar columns, an identity column is often introduced as a surrogate key.

The SQL:1999 standard allowed the creation of tables based on existing table definitions. This capability was refined in SQL:2003 to provide more precise control over the columns that should be included or excluded in the new table. The basic syntax is as follows.

The result of the create table … like … statement is an empty table with the same column names, data types, and nullability as the original table. No dependency is established between existing and new tables. The options specify what action should be taken for “special” columns.