how to remove and update primary keys in sql

Question

1.00/5 (1 vote)

See more:

I want to add primary key once it is removing from table.

SQL

alter table tbl_item_rate alter column c_item_code varchar(25)

this query am executing but error throwing..

error id that

Msg 5074, Level 16, State 1, Line 1
The object 'PK_tbl_item_rate' is dependent on column 'c_item_code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN c_item_code failed because one or more objects access this column.

Posted 19-Mar-15 21:16pm

Member 11337367

Updated 19-Mar-15 21:30pm

Maciej Los

v3

Add a Solution

Comments

Maciej Los 20-Mar-15 3:28am

Could you be more specific?

Member 11337367 20-Mar-15 3:29am

alter table tbl_item_rate alter column c_item_code varchar(25)
this query am executing but error throwing..

error id that
Msg 5074, Level 16, State 1, Line 1
The object 'PK_tbl_item_rate' is dependent on column 'c_item_code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN c_item_code failed because one or more objects access this column.

Maciej Los 20-Mar-15 3:32am

Not much information. Please, provide the structure of the table.

Member 11337367 20-Mar-15 4:38am

CREATE TABLE [dbo].[tbl_item_rate](
[c_Item_code] [varchar](10) NOT NULL,
[n_rate] [numeric](18, 2) NULL,
[n_mrp] [numeric](18, 2) NULL,
[n_std_rate] [numeric](18, 2) NULL,
[n_pts_rate] [numeric](18, 2) NULL,
[d_date_from] [datetime] NOT NULL,
[d_date_to] [datetime] NULL,
CONSTRAINT [PK_tbl_item_rate] PRIMARY KEY CLUSTERED
(
[c_Item_code] ASC,
[d_date_from] 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

King Fisher 20-Mar-15 3:41am

Post Script of your table.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-03-19T21:44:00

Solution 1

Because it's the primary key, you can't change it - SQL relies on the PK for it's indexing.
To change the type of teh primary key column, you have to remove teh primary key, change your table, and the re-establish the PK:

SQL

ALTER TABLE tbl_item_rate
DROP CONSTRAINT PK_tbl_item_rate

ALTER TABLE tbl_item_rate 
ALTER COLUMN c_item_code varchar(25)

ALTER TABLE tbl_item_rate
ADD CONSTRAINT PK_tbl_item_rate PRIMARY KEY (c_item_code)

Posted 19-Mar-15 21:44pm

OriginalGriff

Comments

Member 11337367 20-Mar-15 5:05am

I have dropped primary key but not able to add again due to the error am getting..

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'tbl_item_rate'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

OriginalGriff 20-Mar-15 5:15am

Add NOT NULL to your column definition...PK's can't contain null values (because they wouldn't be unique)

Member 11337367 20-Mar-15 5:30am

I made notnull then also error getting..
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tbl_item_rate' and the index name 'PK_tbl_item_rate'. The duplicate key value is (038512076).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

OriginalGriff 20-Mar-15 5:40am

And what do you suppose that message means?

Member 11337367 20-Mar-15 6:11am

I don't understand the error..Please tell that

OriginalGriff 20-Mar-15 6:23am

What part of
"...terminated because a duplicate key was found for the object name 'dbo.tbl_item_rate'..."
and
"The duplicate key value is (038512076)"
did you not understand?