[Kislany]

I have two tables as below:

CREATE TABLE BOOK(
TITLE VARCHAR(30) NOT NULL,
ISBN VARCHAR(9) NOT NULL,
PUBYEAR CHAR(4),
AUTORID INT NOT NULL,
PRIMARY KEY (ISBN));

and

CREATE TABLE BOOKCOPIES(
BISBN VARCHAR(9) NOT NULL,
COPYID INTEGER NOT NULL,
PRIMARY KEY (BISBN, COPYID));

I want somehow to reference the bookcopies to the book table, with a foreign key, so in a query I can see for each book how many copies exist, however I am not sure how. When I enter:

ALTER TABLE BOOKCOPIES ADD CONSTRAINT BISBN FOREIGN KEY (BISBN) REFERENCES BOOK(ISBN);
I get the error message (in Oracle sql): ORA-02298: cannot validate (SYSTEM.BISBN) - parent keys not found

Anyone can help me out with this problem, please? Just started with sql, so I have a long way ahead of me still...

Thanks,
K

[r937]

first of all, you don't actually need to declare foreign keys in order to write a query that joins two tables -- you can join them anyway

foreign keys are intended to ensure relational integrity, i.e. you would not be able to delete a book if a bookcopy row for it still exists, and you would not be able to add a bookcopy row if the parent book does not exist

as far as your oracle syntax is concerned, i have no idea -- your ALTER syntax looks okay, except you're using the same constraint name as the foreign key name, oracle might not like that

the other thing that might cause an error is if you're adding a foreign key to a table that already has rows in it, and some of those rows would violate the constraint


rudy
http://r937.com/

[Kislany]

You were right with the second point. I entered two rows in the bookcopy table, but only one row in the book table, so there was an extra bisbn number referencing an isbn which didn't actually exist.

Thanks for the quick help.
K.

[satish_ct]

Hi,

THis is because you can't have a record in the table with a foreign key constraint for which no corresponding primary key value exists.

Originally posted by Kislany
I have two tables as below:

CREATE TABLE BOOK(
TITLE VARCHAR(30) NOT NULL,
ISBN VARCHAR(9) NOT NULL,
PUBYEAR CHAR(4),
AUTORID INT NOT NULL,
PRIMARY KEY (ISBN));

and

CREATE TABLE BOOKCOPIES(
BISBN VARCHAR(9) NOT NULL,
COPYID INTEGER NOT NULL,
PRIMARY KEY (BISBN, COPYID));

I want somehow to reference the bookcopies to the book table, with a foreign key, so in a query I can see for each book how many copies exist, however I am not sure how. When I enter:

ALTER TABLE BOOKCOPIES ADD CONSTRAINT BISBN FOREIGN KEY (BISBN) REFERENCES BOOK(ISBN);
I get the error message (in Oracle sql): ORA-02298: cannot validate (SYSTEM.BISBN) - parent keys not found

Anyone can help me out with this problem, please? Just started with sql, so I have a long way ahead of me still...

Thanks,
K