How to remove identity column in SQL table?

Question

0.00/5 (No votes)

See more:

Hi I have in issue in MSSQL 2014 table. In my Project, I have lots of table(136)

Now I need to delete the identity column 'SNo' When I used normal command 'ALTER TABLE AD_FGMaster DROP COLUMN SNo '

Error is "The object 'PK_AD_FGMaster' is dependent on column 'SNo'.

Because it is tied up to so many View. It is difficult to find.
Is there any way drop the column in table as well as in view also?

Pls advice me.

Thank you
maideen

What I have tried:

I have tried this code "ALTER TABLE AD_FGMaster DROP COLUMN SNo"

But error is
Msg 5074, Level 16, State 1, Line 5
The object 'PK_AD_FGMaster' is dependent on column 'SNo'.
Msg 5074, Level 16, State 1, Line 5
The object 'FK_AD_FGMaster_sno' is dependent on column 'SNo'.
Msg 5074, Level 16, State 1, Line 5
The object 'FK_AD_FGMaster_sno' is dependent on column 'SNo'.
Msg 4922, Level 16, State 9, Line 5
ALTER TABLE DROP COLUMN SNo failed because one or more objects access this column.

Posted 29-Sep-16 22:48pm

Maideen Abdul Kader

Updated 29-Sep-16 22:57pm

Add a Solution

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 · Accepted Answer · 2016-09-29T22:57:00

Solution 1

What that means is that your table has the IDENTITY column set as a primary key, and you can't just delete a primary key - you would need to remove the key first, or designate a different column as PK first.

But...are you sure you want to do that? Primary keys are important - they prevent two otherwise identical rows colliding and causing major problems, as well as often acting as the linking field between two tables in a FOREIGN KEY relationship. If you remove the IDENTITY column, you will quite likely damage the rest of your database beyond hope of repair.

Instead of deleting it, try creating a new table without it, and copy all the records. If that works, you don't have duplicates. You can now archive your "old" table, delete it, and rename the "new". If that all works, you haven't got foreign key problems either.
But I'd think long and hard before I deleted a primary key column!

Posted 29-Sep-16 22:57pm

OriginalGriff

Comments

Maideen Abdul Kader 30-Sep-16 5:05am

Thanks for prompt reply.
You are right that Primary Key is important. First I will delete all and create new one. Because Some table primary key naming are total wrong like 'SNo' and 'SONo' from the previous programer. Now I need change all as 'ID'.
It is hard to find in views there are lots of views(210) table(136). This is the my Problem. I newly appointed to rectify this. Is there any Idea?
Pls advice Me...

OriginalGriff 30-Sep-16 5:25am

First off, I'd make absolutely sure I was working with a development copy of the DB, and not one that any other user is trying to access - and definitely not the production copy! :laugh:
Then, I'd back it up - because there will be mistakes.
Then, I'd look at what software is using the existing format - because that's going to need to be changed in parallel.
Then I'd use SSMS to script the entire DB structure to a text file, and start working out exactly what is related to what.
Then I'd write a converter: input the original DB, output a new DB with the new (proposed) structure and copy the data.
Then the new DB that against the modified software. Iterate round till it work in dev and you end up with a converter you can run against the production DB to convert its structure and copy its data (once you've backed that up as well), and release the revised software.

But as far as I know, there is no simple solution - you are looking at a lot of mess that needs cleaning up and most of that is going to require manual work of some form! The important thing is to create a tool so that when you are ready you can "go live" with minimal impact on the production side, and that the software that uses the DB is also prepped to work with the new structure at the same time. Otherwise you are going to be in some serious problems on release day, or be shutting down production for a week or two to migrate it.

Maideen Abdul Kader 30-Sep-16 6:39am

Thank you very much for your view and advice. I agree with you.
There are lots work to do. I will start soon on next week.
Thank you,
maideen

OriginalGriff 30-Sep-16 6:43am

You're welcome!