Constraints in Nested table

In my previous blog “Working With Nested Table Oracle ” i have explained about 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 nested table. we can apply constraints

for unique key:

alter table emp_tab_index add constraint
emps_empno_unique unique (empno);

now we cannot insert duplicate data.

If you want to create Primary key then

Create table as:

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);

: NOTICE main table does not have Primary key, because one table can have only on e PK


comments powered by Disqus