Re: 1:c - relation in oracle

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 24 Dec 2003 08:08:12 -0500
Message-ID: <TPCdncjOSoyZDXSi4p2dnA@comcast.com>

almost possible without triggers by using a unique or PK constraint that includes a sequence number column (which is likely there anyway as the second column of the detail table's primary key) that has a check constraint

however, the limitation is that the maximum number of 'detail' records can only be determined by deriving or maintaining an actual count, which will require a trigger or a PL/SQL package. this is illustrated below with a trigger, derived columns, and a deferred constraint

hopefully this list illustrates the various permutations of 1:M

1:M -- FK
1:M >1 -- FK, NN
1:M =0,1 (actually 1:1 optional) -- FK, UK
1:M =1 (actually 1:1 required) -- FK, UK, NN
1:M >1 <=x -- FK, NN, UK/PK, CK + sequence number column
1:M = x -- FK, NN, deferred CK + count column

FK: Foreign Key constraint
NN: Not Null constraint
UK: Unique constraint
CK: Check constraint

1)
create table invoice_items(
.... invoice_id number

constraint invitm_invid_fk references invoices

2)
create table invoice_items(
.... invoice_id number

            not null
            constraint invitm_invid_fk references invoices

3)
create table invoice_items(
.... invoice_id number

            constraint invitm_invid_fk references invoices
            constraint invitm_invid_uk unique

4)
create table invoice_items(
.... invoice_id number

            not null
            constraint invitm_invid_fk references invoices
            constraint invitm_invid_uk unique

5)
create table invoice_items(
.... invoice_id number

            not null -- actually implied by PK constraint
            constraint invitm_invid_fk references invoices
    , item_seqno number (n,0)
            not null -- implied by PK constraint
            constraint invitm_seqno_ck check (item_seqno between 1 and x)

, constraint invitm_pk primary key(invoice_id, item_seqno)

a scale of 0 for ITEM_SEQNO is required to restrict it to integers, and the between clause restricts it to a specific set of integers which, in combination with a primary key or unique constraint, implies the maximum number of rows (per parent)

6)
to enforce and absolute number of detail records, add a column to the 'parent' table to hold the derived count, plus a deferred check constraint, and then add a trigger to the 'child' table that increments/decrements the parent's derived count column. the deferred constraint will be enforced at commit time -- note, however, that if the deferred constraint is violated, the transaction is rolled back.

this approach does not require a constraint on the sequence number column of the detail table -- for that matter it does not require the sequence number column at all

alter table invoices
add item_count number constraint item_count_chk check (item_count in (0,x)) deferrable initially deferred;

create or replace trigger inv_itm_count
before insert or delete on invoice_items for each row
declare

adj number;
begin

if inserting
then

adj := 1;
else

       adj := -1;
   end if;
   update invoices set item_count = item_count + adj    where id = :old.invoice_id;
end;

for performance (to avoid multiple updates to the parent table) the trigger could maintain a counter in a package variable -- this approach would require a before statement trigger to clear the package variable (likely a pl/sql index by table) and an after statement trigger to issue one update for each affected parent. so now we're up to 3 triggers and a package to implement the business rule

note that for scenarios 5 and 6, the actual business rule is encapsulated in a single check constraint -- either on the sequence number column of the child table, or on the derived counter column of the parent table. changing the single constraint changes (or eliminates) the business rule without affecting any existing data or breaking any code.

-- 
Mark C. Stock
mcstock (at) enquery (dot) com
www.enquery.com training & consulting

Received on Wed Dec 24 2003 - 07:08:12 CST