Re: Default Values

"scotty" <invalid-spammer_at_invalid.invalid> wrote in message news:0grgsugtr309hs7mpf28mqt045nhagr7m8_at_4ax.com...
> Could somone exceptionally bright take alook at this code and suggest
> why the default values are not being inserted?
>
> CREATE TABLE CUSTOMER
> ( CUSTOMER_ID NUMBER(4)
> NOT NULL
> CONSTRAINT gaz PRIMARY KEY,
> TITLE CHAR(4)
> DEFAULT 'mr' <---------- this isnt working???
> CONSTRAINT gaz2
> NOT NULL
> CHECK(title in('mr','miss','mrs')) ,
> SURNAME CHAR(20)
> CONSTRAINT gaz3
> NOT NULL
> CHECK(surname = UPPER(surname)),
> FORENAME CHAR(20)
> CONSTRAINT gaz4
> NOT NULL
> CHECK(forename = UPPER(forename)))
> TABLESPACE "USERS";
According to me the table you have will perform as expected (the below was oracle 9.2)
SQL> ed
Wrote file afiedt.buf

  1 CREATE TABLE CUSTOMER
  2 ( CUSTOMER_ID NUMBER(4)
  3 NOT NULL
  4 CONSTRAINT gaz PRIMARY KEY,
  5 TITLE CHAR(4)
  6 DEFAULT 'mr'
  7 CONSTRAINT gaz2
  8 NOT NULL
  9 CHECK(title in('mr','miss','mrs')) ,  10 SURNAME CHAR(20)
 11 CONSTRAINT gaz3
 12 NOT NULL
 13 CHECK(surname = UPPER(surname)),
 14 FORENAME CHAR(20)
 15 CONSTRAINT gaz4
 16 NOT NULL
 17 CHECK(forename = UPPER(forename)))
 18* TABLESPACE "USERS"
SQL> / Table created.

SQL> insert into customer(customer_id,surname,forename) values(1,'SMITH','JOHN');

1 row created.

SQL> COMMIT; Commit complete.

SQL> SELECT * FROM CUSTOMER; CUSTOMER_ID TITL SURNAME FORENAME

----------- ---- -------------------- --------------------
          1 mr   SMITH                JOHN

SQL> However I would strongly suggest that you change your CHAR fields to Varchar2. Apart from anything else your default value for Title isn't 'mr' but in fact 'mr '. This fact alone might be causing your problems on earlier versions. It also looks somewhat odd to say the least that you want names in caps and titles in lowercase.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************