0

I'm not sure why it's presenting a 'table or view does not exist error' when I try to alter a table right after it is being created in a previous statement. I've included one of the statements with the issue but it happens with another 2. 

CREATE TABLE book_copy (
    branch_code         NUMERIC(2) NOT NULL,
    bc_id               NUMERIC(6) NOT NULL,
    bc_purchase_price   NUMERIC(7,2) NOT NULL,
    bc_reserve_flag     CHAR(1) NOT NULL,
    book_call_no        VARCHAR(20) NOT NULL
);

COMMENT ON COLUMN book_copy.branch_code IS
    'The code of the branch the book refers to';

COMMENT ON COLUMN book_copy.bc_id IS
    'The book crossing ID';

COMMENT ON COLUMN book_copy.bc_purchase_price IS
    'The price of the book';

COMMENT ON COLUMN book_copy.bc_reserve_flag IS
    'Whether or not the book was reserved';

COMMENT ON COLUMN book_copy.book_call_no IS
    'The call number of the book copy';

ALTER TABLE book_copy ADD CONSTRAINT book_copy_pk PRIMARY KEY ( branch_code,
                                                                bc_id );

ALTER TABLE book_copy
    ADD CONSTRAINT book_branch_code_fk FOREIGN KEY ( branch_code )
        REFERENCES branch.branch_code;

ALTER TABLE book_copy
    ADD CONSTRAINT book_call_no_fk FOREIGN KEY ( book_call_no )
        REFERENCES book_detail.book_call_no;






**The Error**
Error starting at line : 39 in command -
ALTER TABLE book_copy
    ADD CONSTRAINT book_branch_code_fk FOREIGN KEY ( branch_code )
        REFERENCES branch.branch_code
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Error starting at line : 43 in command -
ALTER TABLE book_copy
    ADD CONSTRAINT book_call_no_fk FOREIGN KEY ( book_call_no )
        REFERENCES book_detail.book_call_no
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Joshua Nguyen
  • 153
  • 1
  • 8
  • 1
    Please add the error statement in your question. – Arihant Oct 09 '18 at 01:11
  • Have you created the `branch` and `book_detail` tables yet? – Nick Oct 09 '18 at 01:18
  • @Nick I have already created them. As you can see in this https://imgur.com/a/23ENLa9 – Joshua Nguyen Oct 09 '18 at 01:19
  • @Arihant Updated – Joshua Nguyen Oct 09 '18 at 01:20
  • `branch_code` is being assigned as primary key. Then again you are trying to assign it as a `foreign key`. You can't add the primary and foreign key constraint to the same column. Remove it from the `primary key` constraint if it is a foreign key. – Arihant Oct 09 '18 at 01:27
  • But if i need to implement it as a primary foreign key how would i go about doing that? – Joshua Nguyen Oct 09 '18 at 01:31
  • @Arihant I tried running with your comments by the same error persists and it says it doesn't exist – Joshua Nguyen Oct 09 '18 at 01:37
  • Change your references statements to `REFERENCES branch` and `REFERENCES book_detail` and make sure `branch_code` and `book_call_no` are the corresponding primary keys – Arihant Oct 09 '18 at 01:41
  • See this [you shouldn't make foreign as primary](https://stackoverflow.com/questions/10982992/is-it-fine-to-have-foreign-key-as-primary-key) – dwir182 Oct 09 '18 at 01:41
  • I understand that but in this situation I can't as i'm converting from a model into sql. This is the model https://imgur.com/a/06Crbyk for the table. The "bor_no" is the id of the person who borrowed the book. The "branch_code" and "branch_id" identify the book borrowed. – Joshua Nguyen Oct 09 '18 at 01:55
  • The link with you model does not contain self referencing foreign keys, but references to 2 other tables. While it does not match the tables in your question, it is very likely that would be the case for your situation too, so have another look at your model. – Solarflare Oct 09 '18 at 03:41

0 Answers0