Home » SQL & PL/SQL » SQL & PL/SQL » Help with PK constraint
Help with PK constraint [message #266339] Mon, 10 September 2007 10:47 Go to next message
gtriant
Messages: 41
Registered: September 2006
Member
Hi all!

I am trying to create a table and i want to apply a constraint so that one of its columns (father_id) can have as acceptable values, one of the values of another column (node_id).
The problem is that if i use a foreign key from one column to the other, i can't put the first value inside the table, because there are no acceptable values for the father_id.
The table will represent a tree structure so i dont really care for the value of the father_id of the first entry, since the first entry is the root.

create statement:

CREATE TABLE "NODES"
( "NODE_ID" NUMBER NOT NULL ENABLE,
"TITLE" VARCHAR2(400),
"URL" VARCHAR2(400) NOT NULL ENABLE,
"DESCR" VARCHAR2(4000),
"FATHER_ID" NUMBER NOT NULL ENABLE,
CONSTRAINT "NODES_PK" PRIMARY KEY ("NODE_ID") ENABLE,
CONSTRAINT "NODES_UK1" UNIQUE ("URL") ENABLE,
CONSTRAINT "NODES_FATHER_CON" FOREIGN KEY ("FATHER_ID")
REFERENCES "NODES" ("NODE_ID") ON DELETE CASCADE ENABLE
)
Re: Help with PK constraint [message #266347 is a reply to message #266339] Mon, 10 September 2007 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your column must allow null value.

Regards
Michel
Re: Help with PK constraint [message #266352 is a reply to message #266347] Mon, 10 September 2007 11:09 Go to previous messageGo to next message
gtriant
Messages: 41
Registered: September 2006
Member
Actually the not null constraint is an important one. Is there another way that i can get around this?
Re: Help with PK constraint [message #266353 is a reply to message #266347] Mon, 10 September 2007 11:09 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

For more descriptive "FATHER_ID" must not contain any not null constraint.

Cheers
Soumen
Re: Help with PK constraint [message #266354 is a reply to message #266353] Mon, 10 September 2007 11:10 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

If you want a tree structure then you must have to define it as a null field otherwise you can't do that Razz

Cheers
Soumen
Re: Help with PK constraint [message #266355 is a reply to message #266339] Mon, 10 September 2007 11:11 Go to previous messageGo to next message
gtriant
Messages: 41
Registered: September 2006
Member
Thanks all
Re: Help with PK constraint [message #266357 is a reply to message #266339] Mon, 10 September 2007 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>If you want a tree structure then you must have to define it as a null field otherwise you can't do that

Unless of course you defer the constraint until after the table is primed with a couple of necessary records.
Re: Help with PK constraint [message #266375 is a reply to message #266357] Mon, 10 September 2007 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anacedent wrote on Mon, 10 September 2007 18:26
>If you want a tree structure then you must have to define it as a null field otherwise you can't do that

Unless of course you defer the constraint until after the table is primed with a couple of necessary records.

Even then as this must be a tree (not a graph) the root father must be null.

Regards
Michel

Re: Help with PK constraint [message #266397 is a reply to message #266339] Mon, 10 September 2007 14:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Even then as this must be a tree (not a graph) the root father must be null.

Why?

SQL> set term on echo on time on
12:09:03 SQL> @test-pk-tree.sql
12:09:13 SQL> CREATE TABLE "NODES"
12:09:13   2  ( "NODE_ID" NUMBER NOT NULL ENABLE,
12:09:13   3  "TITLE" VARCHAR2(400),
12:09:13   4  "FATHER_ID" NUMBER NOT NULL ENABLE,
12:09:13   5  CONSTRAINT "NODES_PK" PRIMARY KEY ("NODE_ID") ENABLE,
12:09:13   6  CONSTRAINT "NODES_FATHER_CON" FOREIGN KEY ("FATHER_ID")
12:09:13   7  REFERENCES "NODES" ("NODE_ID") ON DELETE CASCADE ENABLE
12:09:13   8  )
12:09:13   9  /
Table created.
12:09:14 SQL> 
12:09:14 SQL> INSERT INTO NODES VALUES(1,'MASTER',1);
1 row created.
12:09:14 SQL> select * from nodes;
   NODE_ID
----------
TITLE
--------------------------------------------------------------------------------
 FATHER_ID
----------
         1
MASTER
         1
Re: Help with PK constraint [message #266399 is a reply to message #266397] Mon, 10 September 2007 14:17 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There you have a loop and this is not a tree but a graph.
It is easy to verify this with "connect by" that only supports trees.

Regards
Michel
Previous Topic: Significance of 'last' in oracle.
Next Topic: Explain plan output.
Goto Forum:
  


Current Time: Sun Dec 11 02:06:29 CST 2016

Total time taken to generate the page: 0.06366 seconds