Re: Help on Data Integrity Constraints

vsatya_at_gmail.com wrote:

> Suppose I have a table "Master" with 3 fields
> Table Name: "Master"
> Columns:
> Type Varchar2(10)
> Code Varchar2(5)
> Desc Varchar2(20)
>
> Combination of Type & Code is the Primary key.
>
> Let's say this table acts like master/setup data to different constants
> that will be used in the system.
>
> Sample Data:
>
> Type Code Desc
> --------------------------------------
> AISTATUS A Active
> AISTATUS I InActive
> ROLE MGR Manager
> ROLE DIR Director
> ROLE SRMGR Senior Manager
> SEX M Male
> SEX F Female
> .....
> .....
>
>
> Values of the "Code" are used to store in other tables like below
>
> Ex:
> TableName: TableA
> columns:
> EmpId Varchar2(5)
> EmpName varchar2(10)
> Status Varchar2(5) -> Possible values are A,I and is determined by
> AISTATUS records of the above table
> Gender Varchar2(5) -> Possible Values are M,F and is determined by SEX
> records of the above table
> Designation Varchar2(5) ->Possible values are MGR,DIR,SRMGR and is
> determined by ROLE records of the above table
>
> Which one of the below approachs is the best way to maintain
> referential(data integrity) constraint between these two tables?
> 1. Use triggers to validate the data.
> 2. Add 3 more columns in this case to the table and add 3 foreign key
> reference constraints to Master Table.
> Updated TableA definition:
> EmpId Varchar2(5)
> EmpName varchar2(10)
> StatusType varchar2(10)
> Status Varchar2(5)
> Foreign key on StatusType,Status referencing Master(Type,Code)
> GenderType varchar2(10)
> Gender Varchar2(5)
> Foreign key on GenderType,Gender referencing Master(Type,Code)
> Des_type Varchar2(10)
> Designation Varchar2(5)
> Foreign key on Des_type,Designation referencing Master(Type,Code)
>
> Any ideas/help on this highly appreciated.
>
> Thanks

Foreign keys, when possible, are almost always prefereable to triggers.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)