Setting a list of default values for new column addition

Question

I am trying to add in a new column to my SCHOOL table in which this column will have 3 possible venues - Building A, Building B and Building C.

I tried coding it as follows

ALTER TABLE SCHOOL
ADD BuildingName NOT NULL
DEFAULT 'Building A', 'Building B', 'Building C' FOR BuildingName;

and I got the following error. Tried enclosing the default values such that it is {'Building A', 'Building B', 'Building C'}, still it is the same error.

Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"

Could it be I am unable to set a list of default values? Or am I setting it wrongly?

Oracle errors come from the Oracle database, so I added the appropriate tag. In general, please tag your questions with the database you are actually using. Also, if you want to add a column to the LESSON table, why does the code change the SCHOOL table? — Gordon Linoff
– Gordon Linoff, Commented Oct 17, 2020 at 12:01

Gordon Linoff · Accepted Answer · 2020-10-17 11:58:50Z

1

I don't think you want a default constraint. I think you want a check constraint.

If you want to limit the values to those three possibilities, then use a check constraint. If you need to add the column:

ALTER TABLE LESSON
    ADD BuildingName VARCHAR2(100) NOT NULL
        CHECK (BuildingName IN ('Building A', 'Building B', 'Building C'));

If you just need to add the constraint (because the column is already in the table):

ALTER TABLE LESSON ADD CONSTRAINT CHK_LESSON_BUILDINGNAME
    CHECK (BuildingName IN ('Building A', 'Building B', 'Building C'));

answered Oct 17, 2020 at 11:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Belayer · Accepted Answer · 2020-10-20 00:12:26Z

A default must contain a single value. Further adding a column and declaring it not null requires a default value. You have few choices:

Select 1 of the valid values to be the default, assigning it to every existing row. Add check constraint as suggested.
Choose a default value that indicates missing or unknown. For example '(unknown)', add that to the list of values in check constraint.
Add the column as nullable and add check constraint as suggested. Then resolve the null values. When all have been resolved then make the column Not Null. You can add a trigger to disallow any new null values until the not null constraint is added.

Each has it own positive and negative points. You will have to analyze them and decide what is best for your application. Also keep in mind that any existing code that inserts will take any default until that code is itself updated.

Collectives™ on Stack Overflow

Setting a list of default values for new column addition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related