[Oracle] About Primary and foreign key design

The primary and Foreign keys have two major features:

1) The primary key itself is a unique index, ensuring the uniqueness of the column where the primary key is located;

2) The foreign key column must have corresponding records in the primary key column of the master table.

You must create an index on the foreign key.

We know that the primary key itself is a unique index, and the foreign key is a constraint. By default, there is no index, but we strongly recommend that you create an index on the foreign key in actual use. The following are two examples:

SQL> alter database mount;Database altered.SQL> alter database open;Database altered.SQL> create table p (id number, name varchar2(30));Table created.SQL> alter table p add constraint pk primary key(id);Table altered.SQL> create table f (id number, pid number, name varchar2(30));Table created.SQL> alter table f add constraint fk foreign key(pid) references p(id);Table altered.SQL> insert into p select rownum,table_name from dba_tables;1206 rows created.SQL> insert into f select rownum,mod(rownum,1000)+1,object_name from dba_objects;14090 rows created.SQL> commit;Commit complete.

The preceding statement creates two tables, where Table p has a primary key and table f has a foreign key. However, we have not created an index on the foreign key. Let's take a look at the execution plan and performance associated with the two tables:

SQL> set autotrace traceonlySQL> set line 1000SQL> select p.id,p.name,f.name from p,f where p.id=f.pid and p.id=880;14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3936432439-------------------------------------------------------------------------------------| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |      |    14 |   840 |    19   (0)| 00:00:01 ||   1 |  NESTED LOOPS                |      |    14 |   840 |    19   (0)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| P    |     1 |    30 |     0   (0)| 00:00:01 ||*  3 |    INDEX UNIQUE SCAN         | PK   |     1 |       |     0   (0)| 00:00:01 ||*  4 |   TABLE ACCESS FULL          | F    |    14 |   420 |    19   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("P"."ID"=880)   4 - filter("F"."PID"=880)Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------          5  recursive calls          0  db block gets        137  consistent gets          0  physical reads          0  redo size       1094  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         14  rows processed

From the execution plan, we can see that a full table scan is performed on table f. Imagine if Table p does not return one record, but multiple records, does table f need to perform multiple scans? The answer is yes. To avoid full table scan for table f, we should create an index on the foreign key, as shown below:

SQL> create index fk on f(pid);Index created.SQL> set autotrace traceonlySQL> set line 1000SQL> select p.id,p.name,f.name from p,f where p.id=f.pid and p.id=880;14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2077701003-------------------------------------------------------------------------------------| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |      |    14 |   840 |    16   (0)| 00:00:01 ||   1 |  NESTED LOOPS                |      |    14 |   840 |    16   (0)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| P    |     1 |    30 |     0   (0)| 00:00:01 ||*  3 |    INDEX UNIQUE SCAN         | PK   |     1 |       |     0   (0)| 00:00:01 ||   4 |   TABLE ACCESS BY INDEX ROWID| F    |    14 |   420 |    16   (0)| 00:00:01 ||*  5 |    INDEX RANGE SCAN          | FK   |    14 |       |     1   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("P"."ID"=880)   5 - access("F"."PID"=880)Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------          9  recursive calls          0  db block gets         90  consistent gets          1  physical reads          0  redo size       1094  bytes sent via SQL*Net to client        524  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)         14  rows processed

From the preceding execution plan, we can know that the f table is indexed instead of a full table scan.

In addition to the above reasons, another advantage of foreign key index building is to avoid lock contention. Let's look at the example below:

First, we delete the index on the foreign key, and then delete a record on table f without submitting the record.

SQL> drop index fk;Index dropped.SQL> delete from f where id=2;1 row deleted.

Next, we opened another session and randomly deleted a piece of data from the p table. We found that it was blocked:

SQL> delete from p where id=2000;

The following are the locks:

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by sid, type;       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK---------- -- ---------- ---------- ---------- ---------- ----------       254 TM      14356          0          3          0          0       254 TM      14359          0          3          0          1       254 TX     655369       1307          6          0          0      1388 TM      14359          0          0          4          0      1388 TM      14356          0          3          0          0

Here we are surprised to find that the table where the primary key p is located, because the table f where the foreign key is located deletes a record at will, the table p is completely locked, no DML operations can be performed. How terrible is this! Easy way to change to a primary key

If the production system has a field in a large table that meets the primary key condition and has no duplicate records, but is only a common index, how can we change it to a primary key?

Because the primary key creation operation is actually to create a unique index and add another constraint, we only need to add one constraint:

SQL> create index normal_idx on p(id);Index created.SQL> alter table p add constraint pk primary key(id);             Table altered.