Constraints in Nested table

April 11, 2017Posted inORACLE

In my previous blog “Working With Nested Table Oracle ” I explained how to work with nested tables. Here we can also define Constraints to the nested table.

According to the previous blog “Working With Nested Table Oracle,” we have Table:= dept_details nested table:= employees with emp_table_type UDT. and stores as emp_tab_index

Now we want to define constraints to the nested tables. we can apply constraints

For unique keys:

Copy Code
alter table emp_tab_index add constraint
emps_empno_unique unique (empno);

Now we cannot insert duplicate data.

If you want to create a Primary key then

Create table as:

Copy Code
CREATE TABLE DEPT_DETAILS
(
   DEPTNO      NUMBER (2) ,--NO PK HERE
   DNAME       VARCHAR2 (14 BYTE),
   LOC         VARCHAR2 (13 BYTE),
   EMPLOYEES   EMP_TABLE_TYPE
)
NESTED TABLE EMPLOYEES
   STORE AS EMP_TAB_INDEX (
      (
      PRIMARY KEY (nested_table_id, EMPNO)
      )
      ORGANIZATION INDEX);


NOTE: The main table does not have a Primary key, because one table can have only one PK

Tags: