Ensuring Data Integrity in Tables With Constraints

Ensuring Data Integrity in Tables With Constraints

With Oracle Database XE, you can define integrity constraints to enforce business rules on data in your tables to preserve the integrity of the data. Business rules specify conditions and relationships that must always be true, or must always be false. For example, in a table containing employee data, the employee e-mail column must be

unique. Similarly, in this table you cannot have two employees with the same employee ID. When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that inserts or modifies data in the table, Oracle Database XE ensures that the new data satisfies the integrity constraint, without the need to do any checking within your program. Any attempt to insert, update, or remove a row that violates a constraint results in an error, and the statement is rolled back. Likewise, any attempt to apply a new constraint to a populated table also results in an error if any existing row violates the new constraint. Constraints can be created and, in most cases, modified with a number of different status values. The options include enabled or disabled, which determine if the constraint is checked when rows are added, modified, or removed; and deferred or immediate, which cause constraint validation to occur at the end of a transaction or at the end of a statement, respectively. You can enforce rules by defining integrity constraints more reliably than by adding logic to your application. Oracle Database XE can check that all the data in a table obeys an integrity constraint faster than an application can. Constraints can be defined at the column level or at the table level:

■ Column-level constraints are syntactically defined where the column to which the constraint applies is defined. These constraints determine what values are valid in the column. When creating a table with Object Browser, the only constraint defined at the column level is the NOT NULL constraint, which requires that a value is included in this column for every row in the table. Table-level constraints are syntactically defined at the end of the table definition and apply to the entire table. With Object Browser, you can create primary key,

foreign key, unique, and check constraints.

Column Default Value

You can define default values that are values that are automatically stored in the column whenever a new row is inserted without a value being provided for the column. When you define a column with a default value, any new rows inserted into the table store the default value unless the row contains an alternate value for the column. Assign default values to columns that contain a typical value. For example, in the employees table, if most employees work in the sales department, then the default value for the department_id column can be set to the ID of the sales department. Depending on your business rules, you might use default values to represent zero or FALSE, or leave the default values as NULL to signify an unknown value. Default values can be defined using any literal, or almost any expression including SYSDATE,

which is a SQL function that returns the current date.

CREATE TABLE personal_info (

employee_id NUMBER(6,0) NOT NULL,

birth_date DATE NOT NULL,

social_security_id VARCHAR2(12) NOT NULL,

marital_status VARCHAR2(10),

dependents_claimed NUMBER(2,0) DEFAULT 1,

contact_name VARCHAR2(45) NOT NULL,

contact_phone VARCHAR2(20) NOT NULL,

contact_address VARCHAR2(80) NOT NULL

);