Home -> Usenet -> c.d.o.misc -> Re: Enforce constraint after design change

Re: Enforce constraint after design change

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Tue, 21 Jul 2009 09:39:51 +0200
Message-ID: <h43rog$im4$1_at_news.eternal-september.org> 





ddf escribió:

> On Jul 20, 7:30 am, "Álvaro G. Vicario"
> <alvaro.NOSPAMTH..._at_demogracia.com.invalid> wrote:




>> 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

>> --- Hide quoted text -

>>

>> - Show quoted text -


> 
> You cannot implement a FAST refresh without a materialized view log.
> Notice the materialized view uses a fast refresh on commit; when
> transactions on the source table are committed then the refresh
> occurs, and in this case the refresh violates the check constraint
> which invalidates the commit on the source table.






It was difficult to grasp the correct syntax. I eventually got this:




CREATE MATERIALIZED VIEW LOG ON TYPE



WITH PRIMARY KEY, ROWID



INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW LOG ON PRODUCT_TO_TYPE

WITH PRIMARY KEY, ROWID



INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW PRODUCT_TYPE_DEPT_CHK

REFRESH FAST ON COMMIT



AS

	SELECT A.PRODUCT_ID, B.DEPT_ID,
         A.ROWID AS ROWID_AT, B.ROWID AS ROWID_TA
	FROM PRODUCT_TO_TYPE A, TYPE B
     WHERE A.TYPE_ID=B.TYPE_ID




/



ALTER TABLE PRODUCT_TYPE_DEPT_CHK



ADD CONSTRAINT PRODUCT_TYPE_DEPT_CHK_PK PRIMARY KEY(


	PRODUCT_ID,
	DEPT_ID




);




I'll do further testing but it seems to work. I suppose that performance 
should not be too bad if TYPE and PRODUCT_TO_TYPE do not change often.




-- 
-- 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 Tue Jul 21 2009 - 02:39:51 CDT












Original text of this message