Reputation: 61
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
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:
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
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