2

If I had a table called person with the fk country_id. Assuming that we have a table called country, which has a default_country column. And all rows except one (for USA), have default_country set to 0, only USA row has default_country to 1.

So if I m inserting person record, and I don't provide country_id, how can I default all new records to have country_id save with the id from row USA.

I know I can set a default column value to id (which I hardcode), but I don't know if its possible to get that from the table, here is what I mean:

"COUNTRY_ID" NUMBER(38,0) DEFAULT -1 CONSTRAINT PR_CN_NOT_NULL NOT NULL

-1 is the hardcoded. it would be nice if I could select it from country table using default_country flag rather than knowing the ID in advance, any ideas?

2
  • How about a trigger?
    – mustaccio
    Commented Jan 9, 2017 at 15:49
  • @mustaccio no can't do it. I mean I can, but I ll do this on the app end. thanks Commented Jan 9, 2017 at 16:41

1 Answer 1

2

It's not possible with DEFAULT. Docs says:

Restrictions on Default Column Values
Default column values are subject to the following restrictions:

A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

The expression can be of any form except a scalar subquery expression.

So DEFAULT (SELECT country_if FROM country where default_country =1 and rownum=1), and even DEFAULT (SELECT 1 FROM DUAL) fail because it cannot contain subquery . You cannot work it around by defining your own function either.
The only way is to create row level before insert trigger .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.