Technique

Constraining what gets entered into a relational database is a good thing. It maintains the data integrity that is so important for database use. You don’t want users to enter the wrong types of information (I.e. letters instead of numbers)

My database class teaches that there are three different ways to constrain input for a database:

1. Declarative Integrity
2. Procedural Application Code
3. Business Procedure

Declarative Integrity means you put restraints directly in the table design. The constraints specify what input the database accepts. More on them below.

When you restrain by using the Procedural Application Code, you put the constraints not in the database tables, but in the programming logic that handles the input of the data (PL/SQL), with actions such as “triggers.”

Business rules or for those cases when you can not (easily) check the integrity of the data by computer, so you put the rules in the employee handbook (“Enter your real birth date, not a false birth date” would be a silly example).

Here’s some basics on Declarative Integrity. When you create a data table, you can add in constraints on what data is accepted. This can be done as part of a column definition, or at the end of the “create table” statement.

Here are the basic types of Integrity Constraints you can use, at least for Oracle databases:

NOT NULL: When an insert is made, a column defined as NOT NULL must be given some data. The NOT NULL declaration goes right after the data type. For instance, when creating the column with the DATE data type, you would add:

[NameOfColumn] DATE NOT NULL,

UNIQUE: UNIQUE requires each new value entered into that column be different from all those value entered before.

PRIMARY KEY: The PRIMARY KEY is the one column that identifies the column from all the others. As such it is considered UNIQUE, meaning each value entered will be different from all the other values entered. However, you don’t use the UNIQUE qualifier when declaring the PRIMARY KEY (it is implied). Here is an example:

[NameOfColumn] [DataType] PRIMARY KEY,

The PRIMARY KEY can be a composite of multiple column entries, which means each each key must be comprised of a unique combination of values from the participating columns. The composite key is defined at the end of the table creation statement, after the last column definition:

constraint [Name_Of_Composite_Primary_Key] PRIMARY KEY ([Name_of_1st_Participating_column], [Name_of_2nd_Participating_column], [...] )

FOREIGN KEY: a FOREIGN KEY uses as its domain of possible values a PRIMARY KEY from another table. This is written as a column definition:

constraint [Name_Of_Foreign_Key] foreign key ([Name_of_External_Column])

references [Name_Of_External_Table]([Name_of_External_Column])

Note, you can not refer to a table in another database, only to another table in the same database. But you can refer to the primary key even in the same table.

The references clause tells the database to delete the dependent row when the corresponding row in the parent table is deleted.

CHECK: The CHECK constraint allows you to specify only certain values can be inserted, as such:

[Name_Of_Column] [Datatype] Check([Name_Of_Column] [operator] [value]),

For example,

Stats VARCHAR2(2) CHECK (Stats => 0)

….means that any values entered for the Stats column must be 0 or higher.

Material taken from this book….

…As well as from a class I’m taking on database design. All mistakes are my own…–Joab Jackson