Can't save a table
-
Hi,
I am trying to change from nvarchar20 to nvarchar50 and I get error I have to drop table and re created. I can't drop table because contains dependancies.What is the best way to go around this?
Thank you
-
sounds like you are changing a column that has FK or something on there that requires a drop and recreate fromt he GUI.
there is a checkbox that prevents that, if you uncheck it, the GUI will drop and recreate with all the right constraints again:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you so much, should I always keep this option unchecked?
-
Dont do GUI. The GUI is very cautious when it comes to altering tables and will more often than not copy data into a temporary table, rename the original, create a new table and import the data back in. Why take that risk when a single line statement will work?
ALTER TABLE dbo.table ALTER COLUMN [Column] varchar(50) -
i leave mine unchecked, because if i'm going to change a table via the GUI, i know it handles all those drop/rename/new cosntraints automatically, so i'm not too worried;
I'm not sure if there would be an adverse impact if you had something like Change Data Capture running or something like that, so there's probably a good reasont hey added that option.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
MysteryJimbo (8/26/2011)
Dont do GUI. The GUI is very cautious when it comes to altering tables and will more often than not copy data into a temporary table, rename the original, create a new table and import the data back in. Why take that risk when a single line statement will work?ALTER TABLE dbo.table ALTER COLUMN [Column] varchar(50)but he's being prevented via the GUI, so it's not a simple ALTER...it's probably a column with an index on it already or a foreign key, so the related tables need THEIR columns resized as well...., becuase of constriants...he'd have to manually dig in find the constraints , script the drops, the recreates, etc. that's a pain.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (8/26/2011)
I'm not sure if there would be an adverse impact if you had something like Change Data Capture running or something like that, so there's probably a good reasont hey added that option.Table availability, database free space, transaction logs etc.
Dont do this with a large table under any circumstances.
-
Lowell (8/26/2011)
MysteryJimbo (8/26/2011)
Dont do GUI. The GUI is very cautious when it comes to altering tables and will more often than not copy data into a temporary table, rename the original, create a new table and import the data back in. Why take that risk when a single line statement will work?ALTER TABLE dbo.table ALTER COLUMN [Column] varchar(50)but he's being prevented via the GUI, so it's not a simple ALTER...it's probably a column with an index on it already or a foreign key, so the related tables need THEIR columns resized as well...., becuase of constriants...he'd have to manually dig in find the constraints , script the drops, the recreates, etc. that's a pain.
I disagree.
I've just tested this on a table column with no constraints, fk etc. Ran the statement given which worked instantly. I then did the same through the GUI which went through the whole "process"
-
MysteryJimbo (8/26/2011)
I disagree.
I've just tested this on a table column with no constraints, fk etc. Ran the statement given which worked instantly. I then did the same through the GUI which went through the whole "process"
I just tested that myself, and I concur; a simple table ends up getting recreated because of the varchar size change, regardless.
man that's creepy for me, i'd expect the GUI to be smarter than that. typically i script changes myself, but I use the gui's abilities to handle constraints and stuff to save time.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (8/26/2011)
MysteryJimbo (8/26/2011)
I disagree.
I've just tested this on a table column with no constraints, fk etc. Ran the statement given which worked instantly. I then did the same through the GUI which went through the whole "process"
I just tested that myself, and I concur; a simple table ends up getting recreated because of the varchar size change, regardless.
man that's creepy for me, i'd expect the GUI to be smarter than that. typically i script changes myself, but I use the gui's abilities to handle constraints and stuff to save time.
The GUI is a fickle thing. Look what happens when you use User Mapping from a login dialog. All I did was map the existing login to the database and grant a role permission. At no point did I change the login or the default_language and db.
USE [master]
GO
ALTER LOGIN [test] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, NO CREDENTIAL
GO
USE [master]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [master]
GO
EXEC sp_addrolemember N'db_datareader', N'test'
GO
-
The interesting part is:
You can always add a column at the end, but not in between (which makes sense, since the forme is a simple alter table add column).
It seems like any ALTER TABLE command except ADD COLUMN is prevented. To rename a column, sp_rename is used, which seems to be allowed, too.
The same thing got me a few weeks ago where a coworker asked me why she couldn't alter a table using SSMS even though she's the db owner. Took me a moment to figure it out though...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply