View previous topic :: View next topic
|
Author |
Message |
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
Hi all,
Is there any provision to convert a NULL column into a NOT NULL column which is having data in it?
I tried many options, but went in vain.
ALTER TABLE table1 ALTER COLUMN t1 SET DATA TYPE CHAR(6) NOT NULL;
ALTER TABLE table1 ALTER COLUMN t1 SET NOT NULL;
All are went in vain.
Any help or a clue is appreciated.
Thanks in advance
Raghu |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
You must specify WITH DEFAULT and try, if you are not aDBA then why bother yourself instead send an email to the them. I that don't work then try either of this.
1. Drop the column, and then re-add it using Alter, which should then let me add it as NOT NULL, and reorg.
2. Unload the table, drop/create the table, load the table. |
|
Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
Thanks Rohit for your replay.
I tried alter the column WITH DEFAULT, NOT NULL WITH DEFAULT. In both cases it gives error saying "NULL" is invalid.
I am using DB2 V8.1.
This version is not allowing to drop a column. As you said I can drop the table and create another table with "NOT NULL" column.
Anyway thanks for your comments on my query.
Regards
Raghu |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
DB2 V8.1 probably doesn't support this feature (ask your DBA) and you would possibly have to be on DB2 V9 or up versions. Also try this if you want constraint to be NOT NULL.
|
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Did you think of reformatting the rows which has NULL |
|
Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008 Posts: 193 Location: chennai
|
|
|
|
@Rohit
Thanks for your hind.
I did it.
Quote: |
CREATE UNIQUE INDEX ALTNDEX ON ALTER_EMP(EMPNO);
ALTER TABLE ALTER_EMP ADD PRIMARY KEY(EMPNO); |
The table was created using LIKE verb. So no contraints were created.
Now the table is not accepting any duplicate rows as well as not accepting NULL values in the indexed column.
Thanks agin for your hint.
Regards
Raghuanthan |
|
Back to top |
|
|
|