Pro Oracle Database 11g Administration (33 page)
For example, suppose the EMP table is created with a DEPT_ID column. To ensure that each employee is assigned a valid department, you can create a foreign-key constraint that enforces the rule that each DEPT_ID in the EMP table must exist in the DEPT table.
164
CHAPTER 7 ■ TABLES AND CONSTRAINTS
■
Tip
If the condition you want to check for is a small list that doesn’t change very often, consider using a check constraint instead of a foreign-key constraint. For example, if you have a column that will always be defined to contain either a 0 or a 1, a check constraint is an efficient solution.
You can use several methods to create a foreign-key constraint. The following example creates a foreign-key constraint on the DEPT_ID column in the EMP table:
create table emp(
emp_id number,
name varchar2(30),
dept_id constraint emp_dept_fk references dept(dept_id));
Notice that the DEPT_ID data type isn’t explicitly defined. It derives the data type from the referenced DEPT_ID column of the DEPT table. You can also explicitly specify the data type when you define a column (regardless of the foreign-key definition):
create table emp(
emp_id number,
name varchar2(30),
dept_id number constraint emp_dept_fk references dept(dept_id));
You can also specify the foreign-key definition out of line from the column definition in the CREATE
TABLE statement:
create table emp(
emp_id number,
name varchar2(30),
dept_id number,
constraint emp_dept_fk foreign key (dept_id) references dept(dept_id)
);
And you can alter an existing table to add a foreign-key constraint:
alter table emp
add constraint emp_dept_fk foreign key (dept_id)
references dept(dept_id);
■
Note
Unlike with primary-key and unique-key constraints, Oracle doesn’t automatically add an index to the foreign-key column(s). You must explicitly create indexes on foreign-key columns.
Checking for Specific Data Conditions
A check constraint works well for lookups when you have a short list of fairly static values, such as a column that can be either Y or N. In this situation, the list of values most likely won’t change, and no other information needs to be stored other than Y or N, so a check constraint is the appropriate solution.
If you have a long list of values that needs to be periodically updated, then a table and a foreign-key constraint are a better solution.
165
CHAPTER 7 ■ TABLES AND CONSTRAINTS
Also, a check constraint works well for a business rule that must always be enforced and can be written with a simple SQL expression. If you have sophisticated business logic that must be validated, then the application code is more appropriate.
You can define a check constraint when you create a table. The following defines that the ST_FLG
column must be either 0 or 1:
create table emp(
emp_id number,
emp_name varchar2(30),
st_flg number(1) CHECK (st_flg in (0,1))
);
A slightly better method is to give the check constraint a name:
create table emp(
emp_id number,
emp_name varchar2(30),
st_flg number(1) constraint st_flg_chk CHECK (st_flg in (0,1))
);
A more descriptive way to name the constraint is to embed information in the constraint name that describes the condition that was violated. For example:
CREATE table emp(
emp_id number,
emp_name varchar2(30),
st_flg number(1) constraint "st_flg must be 0 or 1" check (st_flg in (0,1))
);
You can also alter an existing column to include a constraint. The column must not contain any values that violate the constraint being enabled:
SQL> alter table emp add constraint "st_flg must be 0 or 1" check (st_flg in (0,1));
■
Note
The check constraint must evaluate to a true or unknown (NULL) value in the row being inserted or updated. You can’t use subqueries or sequences in a check constraint. Also, you can’t reference the SQL functions UID, USER, SYSDATE, and USERENV, or the pseudo-columns LEVEL or ROWNUM.
Enforcing Not Null Conditions
Another common condition to check for is whether a column is null; you use the NOT NULL constraint to do this. The NOT NULL constraint can be defined in several ways. The simplest technique is shown here: create table emp(
emp_id number,
emp_name varchar2(30) not null);
A slightly better approach is to give the NOT NULL constraint a name that makes sense to you: create table emp(
emp_id number,
emp_name varchar2(30) constraint emp_name_nn not null);
166
CHAPTER 7 ■ TABLES AND CONSTRAINTS
Use the ALTER TABLE command if you need to modify a column for an existing table. For the following command to work, there must not be any NULL values in the column being defined as NOT NULL: SQL> alter table emp modify(emp_name not null);
■
Note
If there are currently NULL values in a column that is being defined as NOT NULL, you must first update the table so that the column has a value in every row.
Disabling Constraints
One nice feature of Oracle is that you can disable and enable constraints without dropping and recreating them. This means you avoid having to know the DDL statements that would be required to recreate the dropped constraints.
Occasionally, you need to disable constraints. For example, you may be trying to truncate a table but receive the following error message:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys Oracle doesn’t allow a truncate operation on a parent table with a primary key that is referenced by an enabled foreign key in a child table. If you need to truncate a parent table, you first have to disable all of the enabled foreign-key constraints that reference the parent table’s primary key. Run this query to determine the names of the constraints that need to be disabled:
select
b.table_name primary_key_table
,a.table_name fk_child_table
,a.constraint_name fk_child_table_constraint
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner')
and b.table_name = upper('&table_name');
For this example, there is only one foreign-key dependency:
PRIMARY_KEY_TABLE FK_CHILD_TABLE FK_CHILD_TABLE_CONST
-------------------- -------------------- --------------------
D_DATES F_SALES F_SALES_FK1
Use the ALTER TABLE statement to disable constraints on a table. In this case, there is only one foreign key to disable:
SQL> alter table f_sales disable constraint f_sales_fk1;
You can now truncate the parent table:
SQL> truncate table d_dates;
Don’t forget to re-enable the foreign-key constraints after the truncate operation has completed, like this:
167
CHAPTER 7 ■ TABLES AND CONSTRAINTS
SQL> alter table f_sales enable constraint f_sales_fk1;
You can disable a primary key and all dependent foreign-key constraints with the CASCADE option of the DISABLE clause. For example, the next line of code disables all foreign-key constraints related to the D_DATES_PK primary-key constraint:
SQL> alter table d_dates disable constraint d_dates_pk cascade;
This statement doesn’t cascade through all levels of dependencies; it only disables the foreign-key constraints directly dependent on D_DATES_PK. Also keep in mind that there is no ENABLE...CASCADE
statement. To re-enable the constraints, you have to query the data dictionary to determine which constraints have been disabled and then re-enable them individually.
Sometimes you run into situations when loading data where it’s convenient to disable all the foreign keys before loading data (perhaps from a schema-level import using the imp utility). In these situations, the imp utility imports the tables in alphabetical order and doesn’t ensure that child tables are imported before parent tables. You may also want to run several import jobs in parallel to take advantage of parallel hardware. In such scenarios, you can disable the foreign keys, perform the import, and then re-enable the foreign keys.
Here’s a script that uses SQL to generate SQL to disable all foreign-key constraints for a user: set lines 132 trimsp on head off feed off verify off echo off pagesize 0
spo dis_dyn.sql
select 'alter table ' || a.table_name
|| ' disable constraint ' || a.constraint_name || ';'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner');
spo off;
This script generates a file named dis_dyn.sql that contains the SQL statements to disable all the foreign-key constraints for a user.
Enabling Constraints
This section contains a few scripts to help you enable constraints that you’ve disabled. Listed next is a script that creates a file with the SQL statements required to re-enable any foreign-key constraints for tables owned by a specified user:
set lines 132 trimsp on head off feed off verify off echo off pagesize 0
spo enable_dyn.sql
select 'alter table ' || a.table_name
|| ' enable constraint ' || a.constraint_name || ';'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner');
spo off;
When enabling constraints, by default Oracle checks to ensure that the data doesn’t violate the constraint definition. If you’re fairly certain that the data integrity is fine and that you don’t need to 168
CHAPTER 7 ■ TABLES AND CONSTRAINTS
incur the performance hit by revalidating the constraint, you can use the NOVALIDATE clause when re-enabling the constraints. Here’s an example:
select 'alter table ' || a.table_name
|| ' modify constraint ' || a.constraint_name || ' enable novalidate;'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner');
The NOVALIDATE clause instructs Oracle not to validate the constraints being enabled, but it does enforce that any new DML activities adhere to the constraint definition.
In multiuser systems, the possibility exists that another session has inserted data into the child table while the foreign-key constraint was disabled. If that happens, you see the following error when you attempt to re-enable the foreign key:
ORA-02298: cannot validate (
SQL> alter table f_sales enable novalidate constraint f_sales_fk1; To clean up the rows that violate the constraint, first ensure that you have an EXCEPTIONS table created in your user. If you don’t have an EXCEPTIONS table, use this script to create one: SQL> @?/rdbms/admin/utlexcpt.sql
Next, populate the EXCEPTIONS table with the rows that violate the constraint, using the EXCEPTIONS INTO clause:
SQL> alter table f_sales modify constraint f_sales_fk1 validate
exceptions into exceptions;
This statement still throws the ORA-02298 error as long as there are rows that violate the constraint.
The statement also inserts records into the EXCEPTIONS table for any bad rows. You can now use the ROW_ID column of the EXCEPTIONS table to remove any records that violate the constraint.
Here you see that one row needs to be removed from the F_SALES table: SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME CONSTRAINT
------------------ ---------- --------------- ---------------
AAAFVmAAFAAAAihAAA INV_MMGT F_SALES F_SALES_FK1
To remove the offending record, issue a DELETE statement:
SQL> delete from f_sales where rowid = 'AAAFVmAAFAAAAihAAA';
If the EXCEPTIONS table contains many records, you can run a query such as the following to delete by OWNER and TABLE_NAME:
delete from f_sales where rowid in
(select row_id
from exceptions
where owner=upper('&owner') and table_name = upper('&table_name')); You may also run into situations where you need to disable primary-key or unique-key constraints.
For example, you may want to perform a large data load, and for performance reasons want to disable 169
CHAPTER 7 ■ TABLES AND CONSTRAINTS
the primary-key and unique-key constraints. You don’t want to incur the overhead of having every row checked as it’s inserted.
The same general techniques used for disabling foreign keys are applicable for disabling primary or unique keys. Run this query to display the primary-key and unique-key constraints for a user: select
a.table_name
,a.constraint_name
,a.constraint_type
from dba_constraints a
where a.owner = upper('&table_owner')
and a.constraint_type in ('P','U')
order by a.table_name;
Here’s some sample output:
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
DEPT SYS_C006507 P
D_DATES D_DATES_UK1 U
D_DATES D_DATES_PK P
When the table name and constraint name are identified, use the ALTER TABLE statement to disable the constraint:
SQL> alter table d_dates disable constraint d_dates_pk;
■
Note
Oracle doesn’t let you disable a primary-key or unique-key constraint that is referenced in an enabled foreign-key constraint. You first have to disable the foreign-key constraint.
Summary
This chapter focused on basic activities related to creating and maintaining tables. Tables are the containers that store the data within the database. Key table-management tasks include modifying, moving, deleting from, shrinking, and dropping. You must also be familiar with how to implement and use special table types such as temporary, index-organized, and read-only.