Re: Enforce constraint after design change

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Mon, 20 Jul 2009 14:30:04 +0200
Message-ID: <h41o0f$eli$1_at_news.eternal-september.org>

ddf escribió:
> On Jul 17, 8:00 am, "Álvaro G. Vicario"
> <alvaro.NOSPAMTH..._at_demogracia.com.invalid> wrote:

>> We have products:
>>
>> PRODUCT:
>>      PRODUCT_ID PRODUCT_NAME
>>      ========== ============
>>             100 Foo
>>             200 Bar
>>
>> We have product types:
>>
>> TYPE:
>>      TYPE_ID TYPE_NAME
>>      ======= =========
>>            1 Regular
>>            2 Extra
>>
>> Products belong to types:
>>
>> PRODUCT_TO_TYPE:
>>      PRODUCT_ID TYPE_ID
>>      ========== =======
>>             100       1
>>             200       2
>>
>> A product belongs to at most one type: there's a unique index on
>> PRODUCT_ID so you can't do this:
>>
>> PRODUCT_TO_TYPE:
>>      PRODUCT_ID TYPE_ID
>>      ========== =======
>>             100       1
>>             200       2
>>             100       2 <-- violated constrain
>>
>> At this point, there was a feature request. We have departments:
>>
>> DEPARTMENT:
>>      DEPT_ID DEPT_NAME
>>      ======= ============
>>           10 ACCOUNTING
>>           20 SALES
>>
>> and they want that product type is a per-department setting. So my first
>> idea was to add a new column to the TYPE table:
>>
>> TYPE:
>>      TYPE_ID TYPE_NAME DEPT_ID
>>      ======= ========= =======
>>            1 Regular        10
>>            2 Extra          10
>>            3 Regular        20
>>            4 Plus           20
>>
>> And now the new relationships:
>>
>> PRODUCT_TO_TYPE:
>>      PRODUCT_ID TYPE_ID
>>      ========== =======
>>             100       1
>>             200       2
>>             100       4
>>             200       3
>>
>> But, of course, I have to remove the unique index on PRODUCT_ID. The
>> problem is: how do I enforce my new condition? Now a product belongs to
>> at most one type *and* department combination; PRODUCT_TO_TYPE contains
>> no infor about departments.
>>
>> I see no reasonable way to do it with the current design (a BEFORE
>> INSERT/UPDATE trigger that runs a SQL query does not seem reasonable,
>> does it?).
>>
>> Can you think of a simple design change that could help?
>>
>> Thank you in advance.
>>
>> --
>> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
>> -- Mi sitio sobre programación web:http://borrame.com
>> -- Mi web de humor satinado:http://www.demogracia.com
>> --

>
> This may not be simple but it does work:
>
> SQL> create table product(
> 2 product_id number not null,
> 3 product_name varchar2(30) not null
> 4 );
>
> Table created.
>
> SQL>
> SQL> alter table product
> 2 add constraint product_pk
> 3 primary key(product_id);
>
> Table altered.
>
> SQL>
> SQL> create table prod_type(
> 2 type_id number not null,
> 3 type_name varchar2(30) not null
> 4 );
>
> Table created.
>
> SQL>
> SQL> alter table prod_type
> 2 add constraint prodtype_pk
> 3 primary key(type_id);
>
> Table altered.
>
> SQL>
> SQL> create table department(
> 2 dept_id number not null,
> 3 dept_name varchar2(20) not null
> 4 );
>
> Table created.
>
> SQL>
> SQL> alter table department
> 2 add constraint department_pk
> 3 primary key(dept_id);
>
> Table altered.
>
> SQL>
> SQL> create table dept_type(
> 2 dept_type_cd number not null,
> 3 dept_id number not null,
> 4 type_id number not null
> 5 );
>
> Table created.
>
> SQL>
> SQL> alter table dept_type
> 2 add constraint dept_type_pk
> 3 primary key(dept_type_cd);
>
> Table altered.
>
> SQL>
> SQL> alter table dept_type
> 2 add constraint dept_type_uk
> 3 unique(dept_id, type_id);
>
> Table altered.
>
> SQL>
> SQL> create table product_to_type(
> 2 prod_type_cd number not null,
> 3 product_id number not null,
> 4 dept_type_cd number not null
> 5 );
>
> Table created.
>
> SQL>
> SQL> alter table product_to_type
> 2 add constraint proddepttype_pk
> 3 primary key(prod_type_cd);
>
> Table altered.
>
> SQL>
> SQL> alter table product_to_type
> 2 add constraint prodid_fk
> 3 foreign key(product_id)
> 4 references product;
>
> Table altered.
>
> SQL>
> SQL> alter table product_to_type
> 2 add constraint prodtype_fk
> 3 foreign key(dept_type_cd)
> 4 references dept_type;
>
> Table altered.
>
> SQL>
> SQL> insert all
> 2 into product
> 3 values(100, 'Foo')
> 4 into product
> 5 values(200, 'Bar')
> 6 into prod_type
> 7 values(1, 'Regular')
> 8 into prod_type
> 9 values(2, 'Extra')
> 10 into department
> 11 values(10, 'ACCOUNTING')
> 12 into department
> 13 values(20, 'SALES')
> 14 into dept_type
> 15 values(1, 10, 1)
> 16 into dept_type
> 17 values(2, 10, 2)
> 18 into dept_type
> 19 values(3, 20, 1)
> 20 into dept_type
> 21 values(4, 20, 2)
> 22 into product_to_type
> 23 values(101, 100, 1)
> 24 into product_to_type
> 25 values(102, 100, 2)
> 26 into product_to_type
> 27 values(201, 200, 3)
> 28 into product_to_type
> 29 values(202, 200, 4)
> 30 select * from dual;
>
> 14 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> create materialized view log on product_to_type with rowid,
> (product_id) including new values;
>
> Materialized view log created.
>
> SQL>
> SQL> create materialized view prod_type_dept_ck
> 2 refresh fast on commit
> 3 as
> 4 select product_id, count(*) ct from product_to_type
> 5 group by product_id
> 6 /
>
> Materialized view created.
>
> SQL>
> SQL> alter table prod_type_dept_ck
> 2 add constraint check_ct
> 3 check(ct < 3)
> 4 /
>
> Table altered.
>
> SQL>
> SQL> select *
> 2 from product_to_type;
>
> PROD_TYPE_CD PRODUCT_ID
> DEPT_TYPE_CD
> ------------ ----------
> ------------
> 101 100
> 1
> 102 100
> 2
> 201 200
> 3
> 202 200
> 4
>
> SQL>
> SQL> insert
> 2 into product_to_type
> 3 values(203, 200, 2);
>
> 1 row created.
>
> SQL>
> SQL> commit;
> commit
> *
> ERROR at line 1:
> ORA-12008: error in materialized view refresh path
> ORA-02290: check constraint (BING.CHECK_CT) violated
>
>
> SQL>
> SQL> select *
> 2 from product_to_type;
>
> PROD_TYPE_CD PRODUCT_ID
> DEPT_TYPE_CD
> ------------ ----------
> ------------
> 101 100
> 1
> 102 100
> 2
> 201 200
> 3
> 202 200
> 4
>
> SQL>
I still have to understand your code to its full extent (thanks!) but I figure out that the key is the materialized view (a feature I haven't studied yet).

Can constraints on materialized views prevent the modification of the source tables? Is that the whole point?

And, does the materialized view log play any role on this or it's there just to provide an acceptable performance?

-- 
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

Received on Mon Jul 20 2009 - 07:30:04 CDT