Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Question Joining two tables (foreign key problem)

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  3. #3
    Join Date
    Dec 2003
    Posts
    3
    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.

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: Joining two tables (foreign key problem)

    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
    SATHISH .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •