| Author |
Topic |
|
ankurgupta26
Starting Member
32 Posts |
 Posted - 2002-10-30 : 05:57:46
|
| I have table with one column w/o any rows. I want add another NOT NULL column to this table."alter table t1 add col1 char(10) not null"the alter table gives me an error "ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'col3' cannot be added to table 't1' because it does not allow nulls and does not specify a DEFAULT definition."Is there any way out ?? to add a NOT NULL column to an existing table.Thanks !AnkuR. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-30 : 06:08:04
|
| If the column doesn't allow null then it has to be populated with something and you have to say what in the statement - hence the default.You are asking for something that is impossible - not just something that is not allowed.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-30 : 06:27:06
|
| It seemes I have found out that way out....two ways..1. Enterprise manager allows to add NOT NULL columns2 add column with default constraint and then drop it !!alter table ts add col3 int not null constraint ts_col3_default default 10alter table ts drop constraint ts_col3_default Let me know your thoughtsThanks !AnkuR. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-30 : 07:44:52
|
| Adding a column not null with a default is ok as the default will tell the server what to place in the field.I suspect enterprise manager will create the field populated with a zero length string.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-10-30 : 08:04:54
|
quote:
2 add column with default constraint and then drop it !!Let me know your thoughts
I don't think that getting into a habit of altering tables in that manner then dropping the constraints is a particularly good idea. I think that it opens up the pathway for errors and inconsistant data. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-10-30 : 08:29:51
|
| I think (some)people have missed line #1 of the topic....."I have table with one column w/o any rows"....w/o = without = none!If a table has no data, adding a column with NOT NULL...should not need defaults to be specified for the new column on existing rows....because there aren't any existing rows.I think the route taken by ankurgupta26 is acceptable....dropping the constraint is fine, as long as it is solely done for a specific purpose...ie the creation of the new column. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-10-30 : 09:07:13
|
quote:
I think the route taken by ankurgupta26 is acceptable....dropping the constraint is fine, as long as it is solely done for a specific purpose...ie the creation of the new column.
That was primarily my concern, that having learnt the method he then chooses to apply it in different circumstances where there are rows with data. I'm not suggesting that that is likely to occur very often, it's just something to be aware of. |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-30 : 09:16:11
|
| Thanks for ur responses."If a table has no data, adding a column with NOT NULL...should not need defaults to be specified for the new column on existing rows....because there aren't any existing rows." ---> My comments: My initial thought was same ...when there are NO ROWS in the table i.e. empty table, an alter statement shud allow me to add a new NOT NULL column. But, that is not the case.Also, I believe that creating/dropping the constraint is not a good idea, But so far it seems that I have no choice.Is it possible to convert a existing NULL column to NOT NULL column in a existing empty table. If there is a way this can be another alternate. Did any of you guys try the enterprise manager (EM) option ?? Just wondering if the EM allows to add NOT NULL columns why doesn't ALTER allow ??Thanks !AnkuR. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-10-30 : 10:31:07
|
| re "Just wondering if the EM allows to add NOT NULL columns why doesn't ALTER allow ?? "I think you'll find (confirmed elsewhere on this site) that EM creates a new table....transfers the existing table contents to the new table, deletes the old table and then renames the new table to the old table.....So while you may think it's issuing a ALTER TABLE statement...in effect it's going with a "CREATE TABLE" statement.Being lazy (and uneducated) I do most of my (minimal) work in EM....for what I want and need to do, it just about jumps over my personal pain threshold. |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-30 : 10:41:03
|
| Ok so that's the reson EM takes so much time. Thanks for the insight.Would appreciate if some one can answer to..."Is it possible to convert a existing NULL column to NOT NULL column in a existing empty table ??? If there is a way this can be another alternate. "Thanks !AnkuR. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-30 : 10:49:48
|
| ALTER TABLE...ALTER COLUMN should allow you to change the column from NOT NULL to NULL, and vice versa. You can also include a WITH NOCHECK directive to have it skip a NOT NULL constraint check on the column if you know it has nulls in it. |
|
|
|
ankurgupta26
Starting Member
32 Posts |
Posted - 2002-10-31 : 04:39:45
|
| Re: - "I think you'll find (confirmed elsewhere on this site) that EM creates a new table....transfers the existing table contents to the new table, deletes the old table and then renames the new table to the old table..... "That's right. I saw that happening using SQL Profiler.Thanks !AnkuR. |
|
|
|
|
Alter table Question