It's not possible to talk about table objects without also talking about constraints. In fact, it's one type of constraint that led to the name RDBMS (Relational Database Management System). Although the constraint type is called referential and not relational, referential constraints are built on the idea of relationships (hence relational).
Many times when talking about constraints, we talk about integrity (for example, referential constraints are commonly referred to as referential integrity). In fact, it is the constraints that help us keep the integrity of the data. According to the Merriam-Webster Thesaurus, the words "integrity" and "corrupt" are "near antonyms," or words of opposite meanings. This makes sense to a database because the last thing you want is corrupted data (as in data that doesn't make sense or is not valid).
You therefore use different types of constraints to help keep the integrity of the data intact. A prime example of this would be a unique constraint on a Social Security Number (SSN) column in your customer table. Since every US citizen is supposed to have a different SSN, you would want to put a constraint on that column to maintain that every row in the customer table has to have a different, or unique, SSN value. Without the constraint in the database, either the application would have to maintain the uniqueness, or you could end up with multiple customers with the same SSN. If those multiple customers just so happened to be the same person, then your data wouldn't be corrupted, but nothing would be stopping someone from putting a different person in the table with a SSN that is already in use by somebody else, leading to corrupted, or incorrect data.
Note: Constraints do not have to exist in the database; they can also be programmatically controlled in the application. A discussion of which is better would is too lengthy for this tutorial. Just make sure that you use one of the ways and don't ignore constraints altogether.
The example in Listing 13 creates a unique constraint on SSN and names it ssn_unq:
Listing 13. Creating a unique constraint
CREATE TABLE customer ( SSN char(11) UNIQUE CONSTRAINT ssn_unq, LName char(50), Age integer, Birthday date );
Note: IDS also allows for the
DISTINCT keyword to be used in place of
UNIQUE. IDS considers these two words to be
Note: Constraints do not have to be named by the DBA. An internal name will be automatically generated for the constraint if the DBA does not specify one. By naming the constraint through the SQL syntax, it can make identifying a constraint violation error easier when that constraint name comes up in an error condition.
Table 2 describes the different types of constraints available:
Table 2. Constraints and their descriptions
|UNIQUE or DISTINCT||Constrains every value in a column to be different|
|Primary key||Uses the unique constraint and allows for referential integrity on this column set|
|Foreign key||Column(s) used in referential integrity to point back to a primary key column|
|Check||An expression on a column that is used to verify the data is valid for the column|
|Not null||Constrains that every row has to have a value for this column|
|Default||Specifies a default value to be used if a value is not given for this column when inserting a new row|
Referential constraints actually rely on other constraints to exist first. Referential constraints exist between two sets of data. The sets of data in Relational databases are known as columns in a table. So, referential constraints exist between two or more columns in one or more tables. Many times these constraints are called primary/foreign key relationships. The reason for this is that the foreign key column references back to the primary key column. Although this is a very common practice, it is not a rule. Referential constraints can exist between two columns, whether those columns are key columns (primary and foreign) or not. Certain rules do have to apply for a referential constraint to be created.
Some of the rules include (not an exhaustive list):
- The column being referenced must be unique, either through a primary key constraint or a unique/distinct constraint definition.
- The columns must be of the same data type or compatible data types.
- The columns must be in the same database.
The example in Listing 14 expands the example from Listing 13 by adding a second table that has a referential constraint definition back to a column in the first table:
Listing 14. Referential constraint on second table
CREATE TABLE customer ( SSN char(11) UNIQUE CONSTRAINT ssn_unq, LName char(50), Age integer, Birthday date ); CREATE TABLE orders ( OrderNumber integer, OrderingCustomer char(11) REFERENCES customer(SSN), OrderTotal money );
Listing 15 provides an example of accomplishing the same thing with a primary Key:
Listing 15. Referential constraint on second table using a primary key
CREATE TABLE customer ( SSN char(11) PRIMARY KEY CONSTRAINT ssn_unq, LName char(50), Age integer, Birthday date ); CREATE TABLE orders ( OrderNumber integer, OrderingCustomer char(11) REFERENCES customer(SSN), OrderTotal money );
It is also possible to have a table that requires multiple columns for uniqueness. If this is the case, then a different syntax is used to define the unique or primary key constraint. A different syntax is also required for the referencing columns.
Note: If multiple columns are used for the unique or primary key, then the same number of columns must be used on the referencing side of the constraint.
Listing 16. Using multiple columns
CREATE TABLE stock ( StockNumber integer, ManufacturerCode char(3), Description char(50), UNIQUE (StockNumber,ManufacturerCode) CONSTRAINT uq_stock ); CREATE TABLE catalog ( CatalogNumber integer, StockNumber integer, ManufacturerCode char(3), FOREIGN KEY (StockNumber, ManufacturerCode) REFERENCES stock (StockNumber, ManufacturerCode) CONSTRAINT fk_catalog );
The foreign key syntax in Listing 16 would also be used to set up a referential constraint even if you used the following syntax to denote a multi-column primary key:
Listing 17. Multi-column primary key
CREATE TABLE stock ( StockNumber integer, ManufacturerCode char(3), Description char(50), PRIMARY KEY (StockNumber, ManufacturerCode) Constraint pk_stock );
A check constraint is an expression put on a column that specifies the valid data domain for that column. The expression is evaluated every time a new value is inserted into the column or an existing value in the column is updated. If the new value does not meet the conditions of the expression, then an error is returned back to the SQL statement. However, if the evaluation of the check constraint returns a NULL value, then the value is accepted and no error is returned.
Listing 18 provides an example of a check constraint where the data domain for an Age of a person has to be between 1 and 124:
Listing 18. Check constraint
CREATE TABLE customer ( SSN char(11), LName char(50), Age integer, Birthday date, CHECK ( 0 < Age AND Age < 125 ) );
The following syntax could also have been used when defining the same check constraint:
CHECK ( Age BETWEEN 1 AND 124)
In this case the values have to be 1 and 124, because
BETWEEN is inclusive of the ends, and if
you used 0, then the Age of 0 would be a valid value. I chose in my
example to not be able to have an age=0 as a valid value.
A NOT NULL constraint prevents the column from including NULL values. A NULL value is an unknown or undefined value. A NULL value is not equal to 0, it is not equal to a blank (or space), and it is not equal to a series of blanks (or spaces). In fact, a NULL value is not even equal to another NULL value.
Therefore, the expression
WHERE ColumnName = NULL is not valid.
WHERE ColumnName IS NULL or
WHERE ColumnName IS NOT NULL. Since a NOT
NULL column does not allow NULL values, sometimes people refer to this
as a required field, since it requires an actual value.
Listing 19. NOT NULL constraint
CREATE TABLE customer ( SSN char(11) NOT NULL CONSTRAINT nn_customer, LName char(50), Age integer, Birthday date );
A DEFAULT constraint is used to supply a default value for the column
when no value is given during an
UPDATE statement. Many times, customer will
use the NOT NULL constraint and the DEFAULT constraint together to
stop errors from occurring. That way if an
INSERT statement doesn't have a value for a
NOT NULL column, it will use the DEFAULT value.
Listing 20 provides an example of putting a DEFAULT constraint on one column and both a DEFAULT and NOT NULL constraint on another column:
Listing 20. DEFAULT constraint
CREATE TABLE customer ( SSN char(11) DEFAULT '000-00-0000' NOT NULL CONSTRAINT nn_customer, LName char(50), Age integer, Birthday date DEFAULT TODAY );