CODEWITHSUNDEEP
sqloracle-database
j.hall
j.hall

Reputation: 61

Setting a list of default values for new column addition

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?

Upvotes: 0

Views: 218

Answers (2)

Belayer
Belayer

Reputation: 14932

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

  1. Select 1 of the valid values to be the default, assigning it to every existing row. Add check constraint as suggested.
  2. Choose a default value that indicates missing or unknown. For example '(unknown)', add that to the list of values in check constraint.
  3. 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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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'));

Upvotes: 1

Related Questions