梦想有多远

Use the following approach to create tables with constraints and indexes:

1. Create the tables with the constraints. NOT NULL constraints can be unnamed and should be created enabled and validated. All other constraints (CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY) should be named and created disabled.
   

   Note: By default, constraints are created in the ENABLED state.

2. Load old data into the tables.
3. Create all indexes, including indexes needed for constraints.
4. Enable novalidate all constraints. Do this to primary keys before foreign keys.
5. Allow users to query and modify data.
6. With a separate ALTER TABLE statement for each constraint, validate all constraints. Do this to primary keys before foreign keys. For example,

   CREATE TABLE t (a NUMBER CONSTRAINT apk PRIMARY KEY DISABLE,

7. b NUMBER NOT NULL);

8.  CREATE TABLE x (c NUMBER CONSTRAINT afk REFERENCES t DISABLE);

   Now you can use Import or Fast Loader to load data into table t.

   CREATE UNIQUE INDEX tai ON t (a);

9.  CREATE INDEX tci ON x (c);

10.   ALTER TABLE t MODIFY CONSTRAINT apk ENABLE NOVALIDATE;

11.  ALTER TABLE x MODIFY CONSTRAINT afk ENABLE NOVALIDATE;

    At this point, users can start performing INSERTs, UPDATEs, DELETEs, and SELECTs on table t.

    ALTER TABLE t ENABLE CONSTRAINT apk;

12. ALTER TABLE x ENABLE CONSTRAINT afk;

    Now the constraints are enabled and validated.

This entry was posted on 星期一, 二月 25th, 2008 at 11:39 and is filed under oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.