Constraints


Relational databases require that information be broken into multiple tables, each storing related data. Keys are used to create references from one table to the other (and thus the term referential integrity), so as to be able to join tables.

Relational databases and joins were discussed in Chapter 45, "Joins."


For relational database designs to work properly, you need a way to ensure that only valid data is inserted into tables. For example, if an Orders table stored order information and OrderItems stored order details, you'd want to ensure that any order IDs referenced in OrderItems exist in Orders. Similarly, any customers referred to in Orders would have to be present in the Customers table.

Although you could perform checks yourself before inserting new rows (do a SELECT on another table to make sure that the values are valid and present), that is generally a practice to avoid because:

  • If database integrity rules are enforced at the client level, every client will have to enforce those rulesand inevitably some clients won't.

  • You'd also have to enforce the rules on UPDATE and DELETE operations.

  • Performing client-side checks is a time-consuming process; having the DBMS do the checks for you is far more efficient.

DBMSs enforce referential integrity by imposing constraints on database tables. Most constraints are defined in table definitions by using a CREATE TABLE or ALTER TABLE statement.

TIP

Constraints force databases to throw errors (rejecting SQL statements) when they are violated. Within ColdFusion, these errors can be trapped using <cftry> and <cfcatch> (covered in Chapter 26, "Error Handling").


CAUTION

There are several different types of constraints, and each DBMS provides its own level of support for them. Refer to your DBMSs documentation before proceeding.


Unique Constraints

Unique constraints are used to ensure that all data in a column (or set of columns) is unique. This is similar to primary keys, but there are some important distinctions:

  • A table may contain multiple unique constraints, but only one primary key is allowed per table.

  • Unique constraint columns may contain NULL values.

  • Unique constraint columns may be modified or updated.

  • Unique constraint column values may never be reused.

  • Unlike primary keys, unique constraints may not be used to define foreign keys.

Primary keys were discussed in Chapter 44, "Basic SQL."


An example of using unique constraints might be an employees table. Every employee has a unique Social Security number, but you would not want to use that for the primary key because it is too long (and you might not want that information to be easily available). So every employee would have a unique employee ID (a primary key) in addition to his Social Security number.

Because the employee ID is a primary key, you can be sure that it'll be unique. But you also might want the DBMS to ensure that Social Security numbers are unique too (to make sure that someone does not make a typo and reuse someone else's number). You could do this by defining a UNIQUE constraint on the Social Security number column.

The syntax for unique constraints is similar to that for other constraints: either the UNIQUE keyword is defined in the table definition or a separate CONSTRAINT is used.

Check Constraints

Check constraints are used to ensure that data in a column (or set of columns) meets a set of criteria that you specify. Common uses of this are:

  • Checking minimum or maximum valuesfor example, preventing an order of 0 items (even though 0 is a valid number).

  • Specifying rangesfor example, making sure that a ship date is greater than or equal to today's date but not greater than a year from now.

  • Allowing only specific valuesfor example, allowing only M or F in a gender field.

In other words, a datatype restricts the type of data that may be stored in a column. Check constraints place further restrictions within that datatype.

The following example creates a table named OrderItems and applies a check constraint to it to ensure that all items have a quantity of greater than 0:

 CREATE TABLE OrderItems (  order_num INTEGER NOT NULL,  order_item INTEGER NOT NULL,  prod_id CHAR(10) NOT NULL,  quantity INTEGER NOT NULL CHECK (quantity > 0),  item_price MONEY NOT NULL ); 

To check that a column named gender contained only M or F, you could do the following:

 CONSTRAINT CHECK (gender LIKE '[MF]') 

TIP

Some DBMSs enable you to define your own datatypes. These are essentially simple datatypes with check constraints (or other constraints) defined. So, for example, you could define your own datatype called gender that would be a single-character text datatype with a check constraint that restricted its value to M or F (and perhaps NULL is unknown).

The advantage of custom datatypes is that the constraints are applied only once (in the datatype definition), and they are automatically applied each time the datatype is used. Check your DBMS's documentation to determine whether user-defined datatypes are supported.




Macromedia ColdFusion MX 7 Certified Developer Study Guide
Macromedia ColdFusion MX 7 Certified Developer Study Guide
ISBN: 0321330110
EAN: 2147483647
Year: 2004
Pages: 389
Authors: Ben Forta

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