Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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 ******************************************Received on Wed Nov 06 2002 - 02:57:05 CST
![]() |
![]() |