Databases Reference
In-Depth Information
--
ALTER TABLE address ADD CONSTRAINT addr_fk1
FOREIGN KEY (cust_id) REFERENCES cust(cust_id);
--
CREATE INDEX addr_fk1 ON address(cust_id)
TABLESPACE reporting_index;
In this script, two tables are created. The parent table is
CUST
and its primary key is
CUST_ID
. The
child table is
ADDRESS
and its primary key is
ADDRESS_ID
. The
CUST_ID
column exists in
ADDRESS
as a foreign
key mapping back to the
CUST_ID
column in the
CUST
table.
Three B-tree indexes are also created; one is automatically created when the primary key constraint
is created. A second index is automatically created when the unique constraint is created. A third index
is explicitly created in the
ADDRESS
table on the
CUST_ID
foreign key column. All three indexes are created
in the
REPORTING_INDEX
tablespace whereas the tables are created in the
REPORTING_DATA
tablespace.
Reporting on Indexes
The index creation details for the example in the prior section can be verified by querying the data
dictionary.
select index_name, index_type, table_name, tablespace_name, status
from user_indexes
where table_name in ('CUST','ADDRESS');
Here is some sample output:
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME STATUS
-------------------- ---------- ---------- --------------- ----------
CUST_PK NORMAL CUST REPORTING_INDEX VALID
CUST_UK1 NORMAL CUST REPORTING_INDEX VALID
ADDR_FK1 NORMAL ADDRESS REPORTING_INDEX VALID
Run the following query to verify the columns on which the indexes are created:
select index_name, column_name, column_position
from user_ind_columns
where table_name in ('CUST','ADDRESS')
order by index_name, column_position;
Here is some sample output:
INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- -------------------- ---------------
ADDR_FK1 CUST_ID 1
CUST_PK CUST_ID 1
CUST_UK1 LAST_NAME 1
CUST_UK1 FIRST_NAME 2
