1

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

Ashwin N Bhanushali
  • 3,872
  • 5
  • 39
  • 59
  • `'0000-00-00'` is not a valid date. If you want to store the absence of a value, use `NULL` –  Mar 31 '12 at 12:45
  • i didn't got your point where should I use NULL- – Ashwin N Bhanushali Mar 31 '12 at 12:50
  • 2
    If you want to store the fact that you do not know the birthday, use a NULL value when inserting the row. That would require the column birthday to be nullable though. If you make the birthday `not null` you will be forced to put some dummy values in there which is a bad idea. –  Mar 31 '12 at 12:53

1 Answers1

2

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.

Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • I'm confused. What's the difference between "birthday" and "date of birth"? –  Mar 31 '12 at 12:54
  • @a_horse_with_no_name, `birthday` means that day each year as well as date of birth. I'm just suggesting that if the OP is using the former meaning and not the latter it would be better to store the date of birth. – Ben Mar 31 '12 at 12:56
  • thanks, I wasn't aware of that distinction (I'm not a native speaker). But I doubt that birtday (as in date of birth) should be `not null`. Btw: the `primary key` syntax **is** valid. You don't have to specify the `constraint` clause with it (Oracle will choose a default name) –  Mar 31 '12 at 12:58
  • Thank you @a_horse_with_no_name, I've clarified the answer on both points. – Ben Mar 31 '12 at 13:04