Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: Foreign Key pointing to ID column in View

    Hey,

    I am trying to create a Foreign Key Constraint pointing to a View. First of all, my base table and view look like this:
    Code:
    CREATE TABLE BASETABLE 
    (
      "ID" NUMBER(38,0), 
      CONSTRAINT "PK_BASE" PRIMARY KEY ("ID")
    );
    
    CREATE OR REPLACE FORCE VIEW 
      MYVIEW ("ID", "SL")
    AS 
      SELECT ID, BZ AS SL
      FROM BASETABLE;
    Now I have a second table where I need to add a Foreign Key Constraint referencing MYVIEW(ID):
    Code:
    ALTER TABLE ANOTHERTABLE 
    ADD CONSTRAINT FK_TO_MYVIEW FOREIGN KEY (SOMECOLUMN) REFERENCES MYVIEW(ID);
    However, that causes an ORA-02270 / no Primary or Unique Key Constraint for column list.
    I've googled for a while and found out that you can add Constraints to Views, but only with the DISABLE NOVALIDATE option, i.e. they would be useless. Doesn't make much sense, does it?

    Is it impossible to have a FK pointing to a View or am I just being stupid? I hope the latter; we really need that FK.

    Thanks for your help,

    Christian

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    02270, 00000, "no matching unique or primary key for this column-list"
    // *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement 
    //         gives a column-list for which there is no matching unique or primary
    //         key constraint in the referenced table.
    // *Action: Find the correct column names using the ALL_CONS_COLUMNS
    //          catalog view
    Why not have FK reference real column on real table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by cmenke View Post
    I've googled for a while and found out that you can add Constraints to Views, but only with the DISABLE NOVALIDATE option
    The NOVALIDATE would mean that the uniqueness of the PK is not validated because the PK is "not validated" not your FK. So it could very well be that the FK constraint would be checked even if the PK is not validated.

    Why don't you just try declaring the PK on the view and see what happens?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Since a view is simply a stored query, it makes no sense to have a foreign key on the view. simply

    ALTER TABLE ANOTHERTABLE
    ADD CONSTRAINT FK_TO_basetable FOREIGN KEY (SOMECOLUMN) REFERENCES basetable(ID);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Feb 2010
    Posts
    2
    Quote Originally Posted by anacedent View Post
    Why not have FK reference real column on real table?
    The "real table" is going to be replaced by a different base table at some point. We don't know the exact structure of that new table yet. I was going to use MYVIEW as an adapter to avoid having to change referencing tables as well, especially as we have about a dozen of those.

    I'll try the DISABLE NOVALIDATE on the PK, if that doesn't work I'll go with FKs on the base table.

Tags for this Thread

Posting Permissions

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