Nested Sets in Oracle (1)


A colleague asked me about nested sets as described in Managing Hierarchical Data in MySQL.

My initial reaction was that is an example of the explosion of parts problem in database theory. (Sorry, but I am unable to find any good online references. This problem is also known as bill of materials.)

In the Oracle RDBMS, there is the Hierarchical Queries which handles data of this structure.

Set Up in Oracle

For this exercise, I used Oracle Database XE (a free product).

Create the table.

The SQL I used in Oracle is as follows via the APEX interface of the XE database. Because Oracle SQL does not have the AUTO_INCREMENT feature, I let the APEX interface of the Oracle XE database create a trigger which creates a unique key every time a row is inserted. The functionality is the same but the Oracle solution involves more work.

CREATE TABLE  "CATEGORY"
   (	"CATEGORY_ID" NUMBER NOT NULL ENABLE,
	"NAME" VARCHAR2(20) NOT NULL ENABLE,
	"PARENT" NUMBER,
	 CONSTRAINT "CATEGORY_PK" PRIMARY KEY ("CATEGORY_ID") ENABLE,
	 CONSTRAINT "CATEGORY_NAME_UK" UNIQUE ("NAME") ENABLE,
	 CONSTRAINT "CATEGORY_PARENT_ID_FK" FOREIGN KEY ("PARENT")
	  REFERENCES  "CATEGORY" ("CATEGORY_ID") ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_CATEGORY"
  before insert on "CATEGORY"
  for each row
begin
    select "CATEGORY_ID_SEQ".nextval into :NEW.CATEGORY_ID from dual;
end; 

/
ALTER TRIGGER  "BI_CATEGORY" ENABLE
/

Note: I have added two (2) extra constraints that were implied in the data model, but not enforced in the MySQL example:

  • CATEGORY_NAME_UK ensures that the name of each category is unique.
  • CATEGORY_PARENT_ID_FK ensures that the ID of the parent already exists in the table.
Advertisements

3 thoughts on “Nested Sets in Oracle (1)

  1. Nested Sets in Oracle (2) « Yet Another OCM

  2. Nested Sets in Oracle (5) « Yet Another OCM

  3. Nested Sets in Oracle (4) « Yet Another OCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s