Automatic Indexing of primary key constraints

Primary Key constraints automatic index creation constraints are used to ensure the integrity of database data. In oracle databases, you can use constraints, triggers, and application code (processes, functions) to achieve data integrity, among the three methods, the constraints are easy to maintain and have the best performance, so the constraints are preferred for data integrity. i. constraint classification 1.Not null: Make sure that the field value cannot be blank. 2. Unique: ensure that the field value is Unique, but do not prevent insertion of multiple null values. 3. primary key: The most common constraint (Primary key constraint). The column values of the Primary key constraint cannot be repeated or NULL. Note that a table can have at most one Primary key constraint, when the primary key constraint is defined, oracle automatically creates an index with the primary key as the key field. 4. foreign key: defines the relationship between the master and slave tables. foreign must be defined on the slave table, but the master table must have the primary key constraint or unique constraint, if froeign key is defined, the data in the external key column must exist in the primary key column of the master table or be NULL. 5. Check: used to force the table row data to meet the conditions, such as the payroll, the worker's salary must be. II. constraint State enable validate: Default. Both the New and Old data meet the constraint rule enable novalidate: the old data may not meet, and the new data must meet the constraint disable validate: it is not allowed to perform any DML operations on a table. It is mainly used in partitioned tables. For Primary keys and unique conventions, the corresponding unique indexes are deleted. However, any disable novalidate data in the constraints status does not meet the constraints, for primary key and unique conventions, the corresponding unique index is deleted. create a primary key constraint to generate an index. After a primary key index is created for a table, whether the Field Meets the constraint is non-null and unique, and an index is automatically created, check whether the constraint can be set to enable validate after the constraint state is disabled and the same record is inserted again. SQL> create table t (I number constraint pk_ I primary key, v number); SQL> insert into t values (1, 2); SQL> insert into t values (3, 4 ); SQL> commit; SQL> select * from t; I V ---------- 1 2 3 4 now there are two records in the table, and then insert the primary key to it with null or the same value. SQL> insert into t values ('', 10); ERROR at line 1: ORA-01400: cannot insert NULL into (" Y ". "T ". "I") SQL> insert into t values (); insert into t values () * ERROR at line 1: ORA-00001: unique constraint (SYS. PK_ I) violated: You can see all the errors. In this case, the primary key cannot be blank or you can repeat whether to create an index SQL> select index_name from user_indexes; INDEX_NAME ------------------------------ PK_ I close the constraint and perform the same operation again. use SQL> alter table t disable novalidate constra Int pk_ I; Table altered. SQL> insert into t values ('', 10); 1 row created. SQL> insert into t values (1, 10); 1 row created. SQL> commit; Commit complete. SQL> select * from t; I v ---------- 1 2 3 4 1 10 10 SQL> select index_name from user_indexes; no rows selected: you can insert data to the table after the constraint is disabled, and the index is automatically deleted. Now activate the constraint SQL> alter table t enable validate constraint pk_ I; alter table t enable validate constraint pk_ I ERROR at line 1: ORA-02437: cannot validate (SYS. PK_ I)-primary key violated because the table's primary key has the same value, it cannot be restored to the enable validate state. test again and reply to enable novalidateSQL> alter table t enable novalidate constraint pk_ I; alter table t enable validate constraint pk_ I ERROR at line 1: ORA-02437: cannot validate (SYS. PK_ I )- Primary key violated also fails, enable novalidate does not check the old data, so it should be able to restore to enable novalidate, but why not? When executing the command, a unique index (for pk_ I) is generated, but the pk_ I column in the table already has a duplicate value. Therefore, a unique index cannot be created. To restore to enable novalidate, you must create a primary key index (the Index automatically deleted when the constraint is disabled, which is not unique) as follows: SQL> create index pk_ I on t (I); Index created. the default index type is non-unique, non-compressed, and non-reverse key B * tree index. Then the data is restored to enable disvalidate. The inserted data cannot be blank or the primary key cannot be repeated. SQL> alter table t enable novalidate constraint pk_ I; Table altered. SQL> insert into t values (); insert into t values () ERROR at line 1: ORA-00001: unique constraint (SYS. PK_ I) violated 4. when a table is given a primary key constraint, an error message is displayed because the stored data does not meet the constraint rules, in some cases, you must correct the data. First, you must find the following table of data that does not meet the constraints. If there is a data SQL statement that does not meet the constraints, select * from t; I v ---------- ---------------------- 1 2 3 4 15 12 15 10 if a table has more data, you can use the following method to find SQL> alter Table t drop constraint pk_ I; table altered. SQL> conn y/123 SQL> @ $ ORACLE_HOME/rdbms/admin/utlexcpt. SQL Table created. SQL> alter table t add constraint pk_ I primary key (I) exceptions into exceptions; SQL> select * from t where rowid in (select row_id from exceptions ); I v ---------- ------------------------ 15 12 15 10 found the duplicate record correction SQL> update t set I = 10 where v = 12; SQL> select * from t; I v ---------- 1 2 3 4 10 12 15 10 create a primary key constraint SQL> alter table t add constraint pk_ I primary key (I); Table altered.