CODEWITHSUNDEEP
oracle-databaseoracle9i
Ashwin N Bhanushali
Ashwin N Bhanushali

Reputation: 3882

Default Values For Columns Oracle 9i

Hi I am running following query in sql plus

CREATE TABLE yii_profiles (
  user_id NUMBER(11) NOT NULL,
  lastname varchar(50)  NOT NULL DEFAULT '',
  firstname varchar(50) NOT NULL DEFAULT '',
  birthday date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (user_id)
);

I am getting following error. Can you tell where am I going wrong. I tried various ways but no success.

ERROR at line 3: ORA-00907: missing right parenthesis

Upvotes: 1

Views: 819

Answers (1)

Ben
Ben

Reputation: 52853

When creating a constraint you should explicitly state the keyword constraint and a name for that constraint so that you get a proper name in the database for this object.

Oracle treats the empty string as null so NOT NULL DEFAULT '' will not work. I've removed this and changed varchar to varchar2 as the future behaviour of varchar is not guaranteed.

CREATE TABLE yii_profiles (
  user_id NUMBER(11) NOT NULL,
  lastname varchar2(50)  NOT NULL,
  firstname varchar2(50) NOT NULL,
  birthday date NOT NULL,
  constraint pk_yii_profiles PRIMARY KEY (user_id)
);

In the same way setting a default value on a not null column is not necessarily a good idea. If you want to capture your users first names, surnames and birthdays do you want to be defaulting them to something?

Either the first name must be included, in which case setting a default value for this column will only create incorrect data, or the data is not required and the column should be nullable. Personally I would remove the default value on birthday as well for the same reason. Also, as a_horse_with_no_name pointed out the 0 doesn't work as a date in Oracle.

If birthday does not mean "date of birth" but instead the day that is an anniversary of your users birth then I would consider changing this and capturing the date of birth rather than the birthday. You'll always be able to calculate the birthday, but working back to the date of birth would be impossible.

Upvotes: 2

Related Questions