0

I am new to SQL and I am currently have an issue with a constraint I am trying to put on one of my columns.

My database models an art gallery and paintings can either be in the gallery or on loan to another gallery. Obviously it can't be both so I was trying to put a constraint on my on_loan table when creating it to stop the painting ID (P_id) of my on_loan table being the same as the P_id in my in_gallery table. This is what I have done so far:

create table in_gallery (
        P_id             NUMBER (10) CONSTRAINT PK_IN_GALLERY PRIMARY KEY,
        CONSTRAINT IN_GALLERY_FK FOREIGN KEY(P_id) REFERENCES painting(P_id) ON DELETE CASCADE);

create table on_loan (
        P_id             NUMBER (10), 
        CONSTRAINT BOOK_IS_IN_GALLERY CHECK(P_id != in_gallery(P_id)));

This comes up with the error:

ERROR at line 3:
ORA-00904: "IN_GALLERY": invalid identifier

How would i fix this error?

Thanks.

Ivaylo Strandjev
  • 69,226
  • 18
  • 123
  • 176
Ryan Gibson
  • 283
  • 2
  • 3
  • 9

1 Answers1

1

You can't reference another table like that in a check constraint.

Check the answers to the question here for some solutions. You could achieve this with a user defined function.

EDIT: Also based on the little bit of information from your post I don't think you have a correct normalized database model. You could have a Gallery table and a Painting table with a relation between the two.

Community
  • 1
  • 1
JodyT
  • 4,324
  • 2
  • 19
  • 31