Oracle nested table constraints

Question

how to add CHECK constraints into a nested table in oracle?

Object types:

Depend_t (depname: varchar2(12), gender: char(1), bdate: date, relationship:varchar2(10))

Dependtb_t table of Depend_t

Emp_t (eno: number(4), ename: varchar2(15), edept: ref dept_t, salary: number(8,2),

dependents: dependtb_t)

Dept_t (dno: number(2), dname: varchar2(12), mgr ref emp_t)
Proj_t (pno: number(4), pname: varchar2(15), pdept ref dept_t, budget: number(10,2))
Work_t (wemp: ref emp_t, wproj: ref proj_t, since: date, hours: number(4,2))

Tables:

Emp of Emp_t (eno primary key, edept references dept, nested table dependents store as dependent_tb)

Dept of Dept_t (dno primary key, mgr references emp)
Proj of Proj_t (pno primary key, pdept references dept)
Works of Works_t (wemp references emp, wproj references proj)

The Emp, Dept, and Proj tables contain tuples for all employees, departments and projects respectively. The attributes of Emp are employee number (eno), name (ename), employee’s department (edept), salary and the set of dependents stored as a nested table. The relationship attribute may have only ‘SPOUSE’ or ‘CHILD’ as values, gender may be ‘M’ or ‘F’, and bdate records the birth date of the dependant.

here how could add the 'M' or 'F' constraint in nested table?

Out of curiosity, why are you using a nested table and not a heap table that's got a foreign key back to the parent table? You could then quite cheerfully add the desired constraints to the child table. — Boneist
– Boneist, Commented Mar 2, 2020 at 9:00

Tony Andrews · Accepted Answer · 2020-03-02 13:11:30Z

3

From Oracle 12C SQL Reference:

Restrictions on Check Constraints

Check constraints are subject to the following restrictions:

...

Conditions of check constraints cannot contain the following constructs:

...

Nested table columns or attributes ...

This is one of many reasons why nested tables are almost never used in real databases, only in courses that explore all the features of Oracle!

answered Mar 2, 2020 at 13:11

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle nested table constraints

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related