Default value if no data entered into field

[chrisgarvey]

I want to enter a default value if no title is entered into this field. For example 'Untitled'?

I have wrote the following to create the table:

CREATE TABLE my_table
(id number not null
,...
,title varchar2(60) DEFAULT 'Untitled' NOT NULL
, ...);

This allows me to create the table fine.

I now want to enter data into the table, I am using the following insert staitment:

INSERT INTO IPhotograph
(PhotoID, Title, SubjectType, DateTaken, PrintSize, Finish, Colour, PrintExVat, ActualPhotoLoca
VALUES (SEQPhotoID.NEXTVAL, '' , 'Still life', '20-sep-2003', 'A3', 'matt', 'multi', '340.44
/

This code still produces an error message saying Title cannot be null. If I take out the '' totally SQL says there are not enough values.

How do i input a blank field in the Title field. Which should then input by defeault 'Untilited' into the Title field.

Any help would be much appreciated,

Chris

 

[sem]

You should omit it:

INSERT INTO IPhotograph
(PhotoID, SubjectType, DateTaken, PrintSize, Finish, Colour, PrintExVat, ActualPhotoLoca
VALUES (SEQPhotoID.NEXTVAL, 'Still life', '20-sep-2003', 'A3', 'matt', 'multi', '340.44...

Regards, Dima

 

[venkatgvi]

Dear Chris
You should not define the title as NOT NULL
the value cannot be null because if u enter an empty data it automatically stores 'untitled' in it

try it

 

[chrisgarvey]

Thanks for your help,

Although when I delete the Title to 'not null' and omitte the title totally when inserting a record.

I get the following error msg:

ORA-00947: not enough values

Any ideas?

Chris

 

[sem]

Can you just calculate them? The number of fields should be the same as the number of values to insert, isn't it obvious enough I'm also not sure I understand your " delete the Title to 'not null'".

Regards, Dima

 

[SantaMufasa]

Chris,

I take issue with Venkatgvi's suggestion to remove "NOT NULL". You can leave the "NOT NULL", ensuring that you still have the "DEFAULT 'Untitled'", then do your INSERT statement as follows:

INSERT INTO IPhotograph
(PhotoID, SubjectType, DateTaken, PrintSize, Finish, Colour, PrintExVat, ActualPhotoLoca...
VALUES (SEQPhotoID.NEXTVAL, 'Still life', '20-sep-2003', 'A3', 'matt', 'multi', '340.44'...
/

Notice I have left off "Title" from the column list AND I have left off an entry from the VALUES list. Oracle will place "Untitled" in the "Title" column for you. That is how Oracle's DEFAULT works: you leave off any mention of it and it takes care of the rest.

Dave
Sandy, Utah, USA @ 16:16 GMT, 09:16 Mountain Time