New to SQL, need some help

[spamjedi]

Its my first time using SQL and I am having trouble with the ADD CONSTRAINT command. I am trying to add a constraint to a column that refers back to the primary key of the table that the column is in.

Here's what I have:

create table project
(p_id NUMBER(6) constraint project_p_id_pk primary key,
project_name VARCHAR2(30),
client_id NUMBER(6),
mgr_id NUMBER(6),
parent_p_id NUMBER(6),
constraint project_client_id_fk foreign key (client_id)
references client (client_id),
constraint project_mgr_id_fk foreign key (mgr_id)
references consultant (c_id));

alter table project
add constraint project_p_id_fk
foreign key (parent_p_id)
references project (p_id);

I want to make project_p_id refer to p_id, but I'm getting this error
ORA-00904: invalid column name at line 3
line 3 being "foreign key (parent_p_id)"

Oh and here is a line from my assignment saying what we have to do:
"parent_p_id references the same table project. Note that you need to use ALTER statement to create this foreign key."

Anyway I'm sure I have made a simple error or something seein as how this is my first time. Thx fer the help.

------------------
-= SySTeM =-
Athlon 850 | | Asus A7V133A
Radeon 7500| | 256 PC 133 Micron
SoundBlaster XGamer 5.1 | | WD 20gig 7200
LiteOn 16x | |Enlight 7237 300 watt psu

[This message has been edited by spamjedi (edited February 02, 2002).]

[spamjedi]

^bumb^
Anyone?

------------------
-= SySTeM =-
Athlon 850 | | Asus A7V133A
Radeon 7500| | 256 PC 133 Micron
SoundBlaster XGamer 5.1 | | WD 20gig 7200
LiteOn 16x | |Enlight 7237 300 watt psu

[Grizzly]

The foreign key alter table statement looks alright to me, but then again I haven't written too many of those. Most of the work I do is with MSSQL, which allows you to map foreign keys visually, which makes you soft http://www.sharkyforums.com/ubb/biggrin.gif

One thing you might want to explore on line 3 is somehow integrate a table reference, since it apparently doesn't see parent_p_id as a valid column name. I would try:

foreign key (project.parent_p_id)

or maybe

foreign key project(parent_p_id)

I'm really not sure, but I'm guessing that since a foreign key is typically from a different table (but not in this case), than it might be looking for you to provide a source table somehow.

Good luck with it.


[This message has been edited by Grizzly (edited February 03, 2002).]

[spamjedi]

Thx Grizzly. I still cant get it to work though. I must be making an error somewhere else. I dunno... http://www.sharkyforums.com/ubb/rolleyes.gif

------------------
-= SySTeM =-
Athlon 850 | | Asus A7V133A
Radeon 7500| | 256 PC 133 Micron
SoundBlaster XGamer 5.1 | | WD 20gig 7200
LiteOn 16x | |Enlight 7237 300 watt psu