Orace: Default column value based on a filter

Question

Hi a developer asked to add a column on a table which will have a default value of 'N', however if the entry has an id = 3 then the default value of this column should be 'Y', is there anyway I can achieve this in oracle?

Answer 1

I agree with the commenters who have mentioned that this is not a good database design. That said, making compromises with database design is not unusual in real-life situations.

I am not sure that a virtual column is what is wanted. The OP asked for a way to have a default; a virtual column works differently than a default constraint (e.g., with a default constraint we can insert a value other than the default into the column. The best route to take might be to use a trigger to set the "default" value:

CREATE OR REPLACE TRIGGER mytrigger
  BEFORE INSERT ON mytable FOR EACH ROW
  WHEN (new.mycolumn IS NULL)
BEGIN
  SELECT DECODE(id, 3, 'Y', 'N') INTO :new.mycolumn FROM dual;
END;
/

A trigger will also work whether you're using Oracle 10g or 11g (both of which you've tagged).

Hope this helps.