|
View previous topic :: View next topic
|
| Author |
Message |
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi all,
I have a doubt in the following case:
| Code: |
CREATE TABLE XX
( NAME CHAR(30) NOT NULL WITH DEFAULT); |
I have seen a table like this . i cannot understand what does NOT NULL WITH DEFAULT both in a same field. Plz have a analze and let me know. |
|
| Back to top |
|
 |
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
This is from the IBM DB2 manual, Hope it helps
NOT NULL WITH DEFAULT
Is used for a column that does not allow null values, but provides a default value.
DEFAULT
The default value assigned to the column in the absence of a value specified on INSERT or LOAD. Do not specify DEFAULT for a ROWID column or an identity column (a column that is defined AS IDENTITY); DB2 generates default values. If a value is not specified after DEFAULT, the default value depends on the data type of the column, as follows:
Data Type Default Value
Numeric
0
Fixed-length string
Blanks
Varying-length string
A string of length 0
Date
CURRENT DATE
Time
CURRENT TIME
Timestamp
CURRENT TIMESTAMP
Distinct type
The default of the source data type
Dave |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Dave,
Thanks for you help.... i still have some clarification i will get back soon. |
|
| Back to top |
|
|
Sahoo007
New User
Joined: 29 Jun 2006
Posts: 1
|
|
|
|
It is ok. But could you please tell what does the following line means?
Varying-length string
A string of length 0 |
|
| Back to top |
|
|
venktv
New User
Joined: 29 Mar 2006
Posts: 59
Location: Montreal
|
|
|
|
Hi
NULL means not a 0 or space, it means a unknown value...so NOT NULL WITH DEFAULT means, the entry should contains the data type default value like ZERO or spaces rather than having UNKNOWN VALUE.
hope this helps |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi all,
Here i m clear with this. while altering a table with the above syntax ie., Not Null with default... It's giving error like the table cannot be altered. Can anyone try and let me know. |
|
| Back to top |
|
|
senthilssg
New User
Joined: 09 Dec 2005
Posts: 64
Location: USA
|
|
|
|
Hi Khamaurutheen,
By using alter table statement; you can?t change the data type of the existing column of the table. But you can add the new column like this
| Code: |
ALTER TABLE EMPLOYEE
ADD EMPNAME2 CHAR (10) NOT NULL WITH DEFAULT; |
The above statement successfully executed fine.
Actually value of new column should contain space after execution of above statement.
Note:
| Code: |
ALTER TABLE EMPLOYEE
ADD EMPNAME2 CHAR (10) NOT NULL; |
It will give error .Because it is not able to assign any value (due to NOT NULL) for new column for existing rows in the table. So you will have to give NOT NULL WITH DEFAULT. Now the SPACE value will be assigned to new column for existing rows in the table.
Please correct me I am wrong
Regards,
Senthil |
|
| Back to top |
|
|
Brian's_song
New User
Joined: 27 Dec 2005
Posts: 21
|
|
|
|
Hi,
NOT NULL ==> The Field can't be 'NO' value, you should be always input data in these fields,or else it will return error code.
NOT HULL WITH DEFAUT ==> The field can't be 'NO' value, if you forget to input the value in the field,it will set the field value as its default vale,such as zero for num,space for char,00-00-00 for date.....
Above is only base on my perdonal experience.Pls correct me if wrong. |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi all,
Thanks for your clarifications. I got the Point..... |
|
| Back to top |
|
|
|
|
