Re: Database Design

From: <Kenneth>
Date: Fri, 02 Jan 2004 20:14:03 GMT
Message-ID: <3ff5c87e.1335119@news.inet.tele.dk>

On 2 Jan 2004 11:26:01 -0800, nandagopalj_at_hotmail.com (Nan) wrote:

>What are the typical reasons for such a database design without
>referential constraints ?

1. Ignorance (most common)
2. Religion : some developers find FK's awkward, which often leads back to 1....
3. The system is so old that FK constraints were not available when the system was implemented. AFAIK, FK's haven't always been available in Oracle.

>My guess is that by doing away with the referential contraints, the
>application performs faster. Further reasoning is that using APIs to
>create/delete/update data would keep the integrity of the data without
>a need for the database referential constraints.

Actually, FK's, when used correctly, will outperform any manual integrity checks !

Furthermore, PK's and FK's prevent code bloat and keep the error handling apart from the regular code, which is commonly accepted as a good/essential programming practice.

Consider the following code :

create table master (mid number(10),mastername varchar2(10));

create table child (mid number(10), cid number(10),childname varchar2(10));

alter table master
add constraint master_pk
primary key(mid);

alter table child
add constraint child_fk
foreign key(mid)
references master(mid);

alter table child
add constraint child_pk
primary key(mid,cid);

create or replace procedure add_child(pMid in number,pCid in number, pChildname in varchar2)
is

   PK_Violation exception;
   pragma exception_init(PK_Violation,-1);    FK_Violation exception;
   pragma exception_init(FK_Violation,-2291); begin   

  insert into child values (pMid,pCid,pChildname);   

  exception
  when PK_Violation then
    dbms_output.put_line('Error : This child key is already present!');
  when FK_Violation then
    dbms_output.put_line('Error : This child has no parent!');  

end add_child;
/

Clear code. If the constraints above were absent, it would look something like this :

create or replace procedure add_child_manually(pMid in number,pCid in number, pChildname in varchar2)
is

   nn binary_integer;
begin   

  select count(*)
    into nn
    from master
   where mid = pmid;    

  if nn > 0 then
    select count(*)

        into nn
        from child
       where mid = pCid
         and cid = pCid;
       
    if nn = 0 then
         insert into child values (pMid,pCid,pChildname);
    else
        dbms_output.put_line('Error : This child key is already

present!');

    end if;     

  else

     dbms_output.put_line('Error : This child has no parent!');   end if;         

end add_child_manually;
/

which performs worse and is much more awkward.

• Kenneth Koenraadt

Received on Fri Jan 02 2004 - 14:14:03 CST