Orace: filter-based default column value

Question

Hi, the developer asked me to add a column to the table that will have a default value of "N", however, if the record has id = 3, the default value of this column should be "Y", anyway I can achieve this in oracle?

Answer 1

11g approach

From Oracle 11g and above, you can do this in one step using VIRTUAL columns .

Test case

 SQL> CREATE TABLE tab_default ( 2 ID NUMBER, 3 flag varchar2(1) GENERATED ALWAYS AS (decode(id, 3, 'Y', 'N')) VIRTUAL 4 ); Table created. SQL> SQL> INSERT INTO tab_default (ID) VALUES (1); 1 row created. SQL> INSERT INTO tab_default (ID) VALUES (3); 1 row created. SQL> INSERT INTO tab_default (ID) VALUES (10); 1 row created. SQL> SELECT * FROM tab_default; ID F ---------- - 1 N 3 Y 10 N SQL> 

So, the DECODE function in the VIRTUAL column declaration handles the requirement for you.

10g approach

You can fulfill the requirement using -

• DEFAULT Value
• AFTER INSERT TRIGGER whenever id = 3

Create a table to have a DEFAULT value of "N". Let the trigger only start when a new row is inserted with the value in id column = 3, so that the trigger updates the value to 'Y'. Else for all other cases, the default value will be "N".

Answer 2

After adding a new column to the table, you can insert the value into the column using the following query:

 update table_name set column_name = ( case when id = 3 then 'Y' else 'N' end ); 

When inserting new entries, you can use the approach below:
1) Define the column when creating the insert request, you can add logic for this when creating the request.
2) Create a trigger in the database that should update the column value after inserting any new row into the table.

Answer 3

This is a very bad database design. It does not respect the normal form of a relational database. I suggest saving this table as it is and creating a new view in the table with an extra column, which is calculated using DECODE or CASE WHEN ...

Answer 4

I agree with commentators who mentioned that this is not a good database design. However, compromises with database design are not unusual in real-world situations.

I'm not sure if a virtual column is needed. The OP asked for a way to default; a virtual column works differently than the default constraint (for example, with a default constraint, we can insert a value other than the default value in the column. A better route would 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; / 

The trigger will also work regardless of whether you use Oracle 10g or 11g (both of which you checked).

Hope this helps.

Answer 5

Create a new table with an extra value column:

 create table table1 as select u.*, case when id=3 then 'Y' ELSE 'N' END value from table2 u