March 21, 2019 at 9:23 am
Folks I tried this
ALTER TABLE [DataExtract].[dbo].[ControlSetting] ALTER COLUMN [ControlKey] varchar(40);
Then I get this error message
Msg 5074, Level 16, State 1, Line 1
The object 'PK_ControlSetting' is dependent on column 'ControlKey'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ControlKey failed because one or more objects access this column.
Can someone please pass the correct syntax for taking care of the PRIMARY KEY and then altering the table column size.
here is the table structure
CREATE TABLE [dbo].[ControlSetting](
[ControlsettingID] [int] IDENTITY(1,1) NOT NULL,
[ControlKey] [varchar](20) NOT NULL,
[ENV] [varchar](10) NOT NULL,
[ControlValue] [varchar](250) NULL,
[ControlKeyDesc] [varchar](250) NULL,
CONSTRAINT [PK_ControlSetting] PRIMARY KEY CLUSTERED
(
[ControlKey] ASC,
[ENV] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
March 21, 2019 at 9:32 am
What do you think of this ?
ALTER TABLE <Table_Name>DROP CONSTRAINT <constraint_name>
--Then alter the size of the column
ALTER TABLE [DataExtract].[dbo].[ControlSetting] ALTER COLUMN [ControlKey] varchar(40);
ALTER TABLE <Table_Name>ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)
March 21, 2019 at 9:34 am
This link shows you how to handle the PRIMARY KEY.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 21, 2019 at 9:39 am
mw_sql_developer - Thursday, March 21, 2019 9:32 AMWhat do you think of this ?ALTER TABLE <Table_Name>DROP CONSTRAINT <constraint_name>--Then alter the size of the column
ALTER TABLE [DataExtract].[dbo].[ControlSetting] ALTER COLUMN [ControlKey] varchar(40);ALTER TABLE <Table_Name>ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)
I think that if you can answer your own question in 10 minutes, that you should have taken a little more time to do research before posting your question to the forum in the first place.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply