Home » Developer & Programmer » Designer » How to Set A Default Record
How to Set A Default Record [message #90605] Wed, 12 May 2004 06:21 Go to next message
Messages: 173
Registered: November 1998
Senior Member
Hi All

I have a problem that I am describing below.

I have one Table say Emp1 wherein EmpID is defined as the Primary Key.I have a second table say Phone which has EmpId as the Foreign Key.Now One Employee can have several Phones so that Emp--[>]Phone relationship is 1--[>]Many.

But I have another field in Phone called DEFAULT.Now I want to ensure that at any time there is always one Phone record that has its DEFAULT field set to TRUE and all others should be FALSE.So if i set one phone record DEFAULT value as TRUE all other Phone Records for that Employee should become FALSE.

Does Oracle give any way so that I can define this restriction in the schema itself.


Re: How to Set A Default Record [message #90606 is a reply to message #90605] Wed, 12 May 2004 11:12 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
No, you need to do this programatically. Doing it in your application's code will be the easiest. If you try to do it using triggers on "Phone" it will be a lot more difficult because you will have a mutating table(you'll typically need a pre trigger, a post trigger and a package to pass global info from the pre to the post).

Try something like this:
if v_default = 'Y' then
  -- reset other rows to 'N'
  update phone set default = 'N' 
  where emp_id = 123
  and default = 'Y';
end if;
  -- now insert/update New record default = 'Y' where emp_id = 123;
Previous Topic: Learning ORACLE
Next Topic: DB design to Automate Business proposal generation
Goto Forum:

Current Time: Mon Dec 06 12:43:28 CST 2021