2 Replies - 5663 Views - Last Post: 13 April 2012 - 03:00 AM

#1 Polantaris  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 12
  • Joined: 02-March 12

Error when adding a default constraint.

Posted 13 April 2012 - 02:10 AM

I'm a little new to Oracle. Regardless, I've been doing assignments in my Database class with usually decent success without much trouble. I hit an issue where I need to add several constraints to a new attribute column in an existing table, and I'm getting errors with the attempts.

I need to alter a table to add a new attribute column, give it a constraint to enforce a domain of three specific values, and a second constraint of a default value.
ALTER TABLE VIDEO
ADD VID_STATUS VARCHAR(4) NOT NULL
ADD CONSTRAINT VID_STAT1 CHECK(VID_STATUS IN('IN','OUT','LOST'))
ADD CONSTRAINT VID_STAT2 DEFAULT VID_STATUS = 'IN';


With that code, I am getting an ORA-00904: Invalid Identifier error on DEFAULT on the 4th line. Before this attempt, I tried combining the constraints into one constraint, and I would get an error on the first opening parenthesis of the following attempted line (Same Error):
ADD CONSTRAINT VID_STAT1(CHECK(VID_STATUS IN('IN','OUT','LOST')) AND DEFAUlT VID_STATUS = 'IN');


I thought I read somewhere that Oracle doesn't support the DEFAULT Constraint? If so, how would I set up a DEFAULT of this nature? My book acts like there is a DEFAULT Constraint that I can use, but an invalid identifier error makes me think there isn't, but I can't find anything to replace it with.

Any help would be great.

Is This A Good Question/Topic? 0
  • +

Replies To: Error when adding a default constraint.

#2 Polantaris  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 12
  • Joined: 02-March 12

Re: Error when adding a default constraint.

Posted 13 April 2012 - 02:44 AM

I can't find an Edit button so:

Nevermind about this, I figured the problem out.

ALTER TABLE VIDEO
ADD VID_STATUS VARCHAR(4) DEFAULT 'IN' NOT NULL
ADD CONSTRAINT VID_STAT1 CHECK(VID_STATUS IN('IN','OUT','LOST'));


The above worked. Sorry for posting this.
Was This Post Helpful? 1
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3717
  • View blog
  • Posts: 5,979
  • Joined: 08-June 10

Re: Error when adding a default constraint.

Posted 13 April 2012 - 03:00 AM

Hey.

Glad you found the solution. Thanks for sharing it!

I edited the topic title a bit, by the way, to make it clearer what the problem was. Might make it easier for some Googler to find your solution.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1