Alter Column - Not NNULL failed
-
Hi
I created one table
--create table test2(a integer,b varchar(12))
created an index on that table
--create index NC_test2 on test2(a) with fillfactor=25
Then if i am trying to alter the column for NOT NULL it is giving an error
alter table test2 alter column a integer not null
Server: Msg 5074, Level 16, State 8, Line 14
The index 'NC_test2' is dependent on column 'a'.
Server: Msg 4922, Level 16, State 1, Line 14
ALTER TABLE ALTER COLUMN a failed because one or more objects access this column.
HOW CAN I MAKE THIS COLUMN AS NOT NULL WITHOUT DROPPING THE INDEX ?
Any idea ?
Regds
SKV
-
Hi,
Just change the order in which you do things logically - make the column NOT NULL at its initial declaration before you create the index:-
create table test2(a integer NOT NULL,b varchar(12))
create index NC_test2 on test2(a) with fillfactor=25
Have fun
Steve
We need men who can dream of things that never were.
-
ok thankx,
but if the index is already created , we need to drop the index and recreate it right ?
-
More Sequel wierdness.....
Had a quick look - If you try to run the code, sure enough you get the error.
Strangely enough, if you go into Entrprise manager, right click the table, select 'Design Table' and uncheck the 'Allow Nulls' option. Close and save. It happily allows you to do it, without even a warning.
There must be some database option you need to change to do it in code. Tried a couple to no avail........
Hope this work around helps.
Have fun
Steve
We need men who can dream of things that never were.
-
Steve - EM 'cheats'. A lot of the time you will see in these forums "but EM changes <X> why can't I!?", and the reason is this: EM is actually creating a new object with the desired properties, copying data over, dropping the old object, and renaming. Really!
-
Thats fair enough matey. No surprise really - As long as the job gets done and all that......
Would have expected a warning at least though.
Have fun
Steve
We need men who can dream of things that never were.
-
As long as the job gets done and all that......
Not really! It could get frustrating, when you do things with EM that takes hours and hours to complete, and complete much faster in QA. Changing a column, move to a new filegroup etc, etc...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thing is EM doesn't always 'get the job done' due to what EM does. In cases like Frank mentions, imo that's failng the job, if EM takes unnecessary time and resources to come to the end result.
My recommendation is to not use EM for any 'write operations' at all! It's dangerous if you don't know what really happens, it's easy to fubar, you have no transactional control - when it's done, it's done. Instead, use EM's ability to create scripts for you - take the scripts into QA, read them and then run them in a controlled fashion. You'll sleep better then
/Kenneth
-
Must admit - when I saw AKM's post, that is my intention now. To use EM to create scripts more often and see what its doing in the background.
One little point though:- If you are struggling to achieve something and EM does it in double or even treble the time it would take with code - it can still be quicker to just get the job done, than it would be to work out how to code it. Especially if you are limited on time - just get the job done and work out how to do it later.
Hope you all had a Good Xmas and a Happy New Year. All the best.
P.S. I always sleep at night - when I finish work, it gets left there 99% of the time
Steve
We need men who can dream of things that never were.
-
A lot of times, for ddl changes, EM will drop and recreate the table. That is why it takes such a long time to complete. Run a trace on it and you will see it happen. You can do the same by creating the new table, inserting the rows into it and creating your index and then rename both objects.
Tom
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply