problem while inserting values

[yuvraj kotegar]

my databe table structure is as follows


name ssn address salary superssn

ok

here foreign key is superssn and reference is also in the same table.

so while inserting values am getting ORA-02291: error how do i resolve this??

[yuvraj kotegar]

superssn is referring ssn

so not able to insert any values at all

[Campbell Ritchie]

I think this thread would sit more happily in another forum . . .

Please provide more details about your insert statement or whatever.
What is "ssn"? What type of variable is it, and also superssn? Which is the primary key in your table.

[yuvraj kotegar]

ssn is a varchar

superssn is also varchar

primary key is ssn


sorry for posting it here. i didnt find any sql forum here in javaranch.

[yuvraj kotegar]

superssn is the foreign key so am not able to insert any values

when i try to insert values it will not the superssn value

for example

say ssn is 1 and superssn is 2 in the first insert .then since 2 is not available at that point . it shows error.

but how do i solve this. some values has to be entered right?

[Henry Wong]

sorry for posting it here. i didnt find any sql forum here in javaranch

Posting to the right forum is a two-way street. You stand a much better chance of having people who can answer your question if you do. You are kinda wasting your time by posting to any random forum.

Regardless, the closest forum for this, is probably the JDBC forum, which hopefully, this topic will be moved to.

Henry

[Campbell Ritchie]

Please provide full details of the table, and the insert statement.

[Heather Rose]

What exactly does your error say? And please provide details on exactly what you are trying to do.

[yuvraj kotegar]

hi

this is my create query

 create table employee (
 
name varchar(25),
ssn varchar(20),
address varchar(20),
salary number,
superssn varchar(20),
primary key(ssn),
foreign key(superssn) references employee(ssn));

[ September 08, 2008: Message edited by: yuvraj kotegar ]

[yuvraj kotegar]

table gets created ok

then when i try to insert values i get the following error

ORA-02291: integrity constraint (SYSTEM.SYS_C003019) violated - parent key not
found

how to insert atleast one record??

[Campbell Ritchie]

You have got the concept that you are inserting a row with a reference to a previous row; the very 1st row cannot refer to other rows, so it either must refer to itself, or to nothing.

For the 1st row use "null" for superssn or use its own value.

If you have designed your table that it not only contains the employee's name but also their supervisor's id, then you are designing a "tree" inside the table ( ) and you ought to have a separate supervisor table.
[ September 09, 2008: Message edited by: Campbell Ritchie ]

[yuvraj kotegar]

this is the solution guys

 
 
ALTER TABLE tableName ADD CONSTRAINT someName
    FOREIGN KEY (attribute) REFERENCES tableName(attribute)
    INITIALLY DEFERRED DEFERRABLE;
 

 

[yuvraj kotegar]

Thanks people for all your time and help

:thumb: