help needed with a foreign key error

Hi all,

I'm new to sql and started practicing creating tables with primary and foreign key constraints.I'm stuck with an error.I have created two tables category_header and route_header.cat_code is the primary key in category_header and cat_code is the foreign key in the table route_header.I started entering values for both the tables.First one created without any problem but the second table is not taking the values.Please take a look at the following code and let me know what went wrong:

SQL> desc category_header;
Name Null? Type
----------------------------------------- -------- ----------------------------
CAT_CODE NOT NULL NUMBER(5)
CAT_DESC VARCHAR2(15)

SQL> desc route_header;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROUTE_ID NOT NULL NUMBER(5)
CAT_CODE NUMBER(5)
ROUTE_NO NOT NULL NUMBER(2)
ORIGIN NOT NULL VARCHAR2(15)
DESTINATION NOT NULL VARCHAR2(15)
FARE NOT NULL NUMBER(2)
DISTANCE NOT NULL NUMBER(3)
CAPACITY NOT NULL NUMBER(2)

SQL> select * from category_header;

CAT_CODE CAT_DESC
---------- ---------------
1 superdeluxe
2 deluxe
3 super fast
4 normal

SQL> select * from route_header;

no rows selected

SQL> insert into route_header values(&route_id, &cat_code, &route_no, '&origin', '&destination', &fa
re,&distance,&capacity);
Enter value for route_id: 101
Enter value for cat_code: 33
Enter value for route_no: 01
Enter value for origin: houston
Enter value for destination: dallas
Enter value for fare: 35
Enter value for distance: 250
Enter value for capacity: 50
old 1: insert into route_header values(&route_id, &cat_code, &route_no, '&origin', '&destination',
new 1: insert into route_header values(101, 33, 01, 'houston', 'dallas', 35,250,50)
insert into route_header values(101, 33, 01, 'houston', 'dallas', 35,250,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_CAT_CODE) violated - parent key not
found

Should the cat_code you're trying to enter be 3 instead of 33?

Oh, I'm sorry.I have corrected that mistake.But still I'm getting the same error. see the following output:

insert into route_header values(&route_id, &cat_code, &route_no, '&origin', '&destination', &fa
re,&distance,&capacity);
Enter value for route_id: 101
Enter value for route_no: 33
Enter value for cat_code: 1
Enter value for origin: houston
Enter value for destination: dallas
Enter value for fare: 35
Enter value for distance: 250
Enter value for capacity: 50
old 1: insert into route_header values(&route_id, &cat_code, &route_no, '&origin', '&destination',
new 1: insert into route_header values(101, 33, 01, 'houston', 'dallas', 35,250,50)
insert into route_header values(101, 33, 1, 'houston', 'dallas', 35,250,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_CAT_CODE) violated - parent key not
found

It looks like your info's not lining up -- you've still got 33 in the cat_code position and 1 in the route_no. So if there's no 33 cat_code in category_header, you can't add a row with a cat_code of 33 into route_header.

Hi,

Thanks for the reply.I have created the first row after lot of stupid mistakes.As you mentioned, I'm not entering values correctly.It is a simple mistake but I thought I did something wrong while creating the first table.Thanks again..