Re: Enforce constraint after design change
Date: Fri, 17 Jul 2009 10:49:00 -0700 (PDT)
Message-ID: <ca0cab8c-d7d9-4e7f-8827-fdea3c816649_at_o7g2000yqb.googlegroups.com>
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 null4 );
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 null4 );
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 null4 );
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> David Fitzjarrell Received on Fri Jul 17 2009 - 12:49:00 CDT