Hey Guys
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
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
No comments:
Post a Comment
Please do not add any spam links or abusive comments.