Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: constraint WHERE

    One of our analysts was asking if there was a mechanism in the ALTER TABLE step which allows for a WHERE clause, like

    Code:
    alter table employees add (
    constraint forkey
    foreign key (division_code)
    references division (division_code) "WHERE stop_date IS NULL"
    Kind of like a check constraint which references the PARENT table, not the child.

    -Chuck

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    No, you're out of luck. There is nothing like that.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What about this idea?

    You could create a VIEW - something like

    CREATE VIEW division_view AS
    SELECT * FROM division
    WHERE stop_date IS NULL;

    and create a referential integrity constraint from the "employees" table to the "division_view" view.

    Would this be possible? (Just in case, here is a link to the Constraints page in Oracle documentation).

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by Littlefoot
    What about this idea?

    You could create a VIEW - something like

    CREATE VIEW division_view AS
    SELECT * FROM division
    WHERE stop_date IS NULL;

    and create a referential integrity constraint from the "employees" table to the "division_view" view.

    Would this be possible? (Just in case, here is a link to the Constraints page in Oracle documentation).
    I had the same idea and tested it, but unfortunately view constraints can only be created DISABLE NOVALIDATE and thus does not seem to be referenceable in a FK constraint :

    Code:
    rbaraer@Ora10g> create table testtable1
    (
       division_code number,
       stop_date date,
       constraint pk_testtable1 primary key(division_code)
    );  2    3    4    5    6
    
    Table created.
    
    rbaraer@Ora10g> insert into testtable1(division_code, stop_date)
       values (1, sysdate-3);  2
    
    1 row created.
    
    rbaraer@Ora10g> insert into testtable1(division_code, stop_date)
       values (2, sysdate-1);  2
    
    1 row created.
    
    rbaraer@Ora10g> insert into testtable1(division_code, stop_date)
       values (3, NULL);  2
    
    1 row created.
    
    rbaraer@Ora10g> create or replace view testview1
    (
       division_code,
       stop_date,
       CONSTRAINT pk_testview1 PRIMARY KEY (division_code) RELY DISABLE NOVALIDATE
    )
    as select division_code, stop_date
    from testtable1
    where stop_date is not null;  2    3    4    5    6    7    8    9
    
    View created.
    
    rbaraer@Ora10g> create table testtable2(
       id number,
       division_code number,
       constraint pk_testtable2 primary key(id),
       constraint fk_tsttbl2_tstview1 foreign key(division_code) references testview1(division_code));  2    3    4    5
    create table testtable2(
    *
    ERROR at line 1:
    ORA-02270: no matching unique or primary key for this column-list
    
    
    rbaraer@Ora10g>
    I thought it would work, too. Maybe I missed something...

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Now, this is probably nonsense: as I don't have a database to try it myself, and if you're still around here, could you check this (an excerpt from the documentation): View constraints are supported only in DISABLE NOVALIDATE mode. You cannot specify any other mode. You must specify the keyword DISABLE when you declare the view constraint.

    So, can we (although it is said that they are supported only in disable mode) - after the view is created - alter the constraint and enable it? I guess not, but ... maybe ... hm?

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by Littlefoot
    Now, this is probably nonsense: as I don't have a database to try it myself, and if you're still around here, could you check this (an excerpt from the documentation): View constraints are supported only in DISABLE NOVALIDATE mode. You cannot specify any other mode. You must specify the keyword DISABLE when you declare the view constraint.

    So, can we (although it is said that they are supported only in disable mode) - after the view is created - alter the constraint and enable it? I guess not, but ... maybe ... hm?
    Nope, the doc specifies one can only set a constraint RELY or NORELY : no way to enable it.

    Code:
    rbaraer@Ora10g> alter view testview1 modify constraint pk_testview1 enable;
    alter view testview1 modify constraint pk_testview1 enable
    *
    ERROR at line 1:
    ORA-00922: missing or invalid option
    
    
    rbaraer@Ora10g> alter view testview1 modify constraint pk_testview1 norely;
    
    View altered.
    
    rbaraer@Ora10g> alter view testview1 modify constraint pk_testview1 rely;
    
    View altered.
    
    rbaraer@Ora10g>
    Nice try though .

    I'm not used to materialized views... could this work with a MV instead of a view ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yet another stupid question, but - where's the purpose of view constraints (primary key ones) if they can only be DISABLED and specified as NOVALIDATE? Sure, use RELY parameter to make them more useful? More useful?!?

    As of materialized views, I really wouldn't know what to say; never tried something like that either.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The purpose of constraints on views is to provide extra useful information to the optimiser, not for validation of data. I understand they are particularly beneficial in data warehousing.

    Yes, you can use constraints on materialized views for this kind of thing:
    Code:
    SQL> create materialized view chicago_dept as 
      2  select * from dept where loc='CHICAGO';
    
    Snapshot created.
    
    SQL> alter table emp add constraint emp_chicago_dept_fk
      2  foreign key (deptno) references chicago_dept(deptno);
    alter table emp add constraint emp_chicago_dept_fk
                                   *
    ERROR at line 1:
    ORA-02298: cannot validate (RB.EMP_CHICAGO_DEPT_FK) - parent keys not found
    That makes sense, because not all employees are in Chicago. Let's pretend they are:
    Code:
    SQL> create table emp2 as select * from emp;
    
    Table created.
    
    SQL> update emp2 set deptno=30;
    
    14 rows updated.
    
    SQL> alter table emp2 add constraint emp_chicago_dept_fk
      2  foreign key (deptno) references chicago_dept(deptno);
    
    Table altered.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In other words, it means that queries on constrained views are executed faster. Did I get it right?

    Also, thank you for the snapshot example!

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes. This recent blog entry from Tom Kyte shows how constraints can enhance query optimisation. It even uses MVs!

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Cool. I've never thought of using 'constraints' in views (In fact, never seem to be aware of such a feature till now).

Posting Permissions

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