如何删除SQL表中的标识列？ [英] How to remove identity column in SQL table?

问题描述

Hi
我在MSSQL 2014表中遇到过问题。在我的项目中，我有很多表（136）



现在我需要删除标识列'SNo'当我使用普通命令'ALTER TABLE AD_FGMaster DROP COLUMN SNO'



错误是对象'PK_AD_FGMaster'依赖于列'SNo'。



因为它与很多View捆绑在一起。很难找到。

是否有任何方法可以放下表中的列以及视图中的列？



请给我建议。



谢谢

maideen



我尝试了什么：



我试过这段代码ALTER TABLE AD_FGMaster DROP COLUMN SNo



但错误是

消息5074，等级16，状态1，行5

对象'PK_AD_FGMaster'取决于列' SNo'。

消息5074，等级16，状态1，行5

对象'FK_AD_FGMaster_sno'取决于列'SNo'。

消息5074，16级，状态1，第5行

对象'FK_AD_FGMaster_sno'取决于列'SNo'。

消息4922，等级16，状态9，行5

ALTER TABLE DROP COLUMN SNo失败，因为一个或多个对象访问此列。

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.

推荐答案

这意味着您的表将IDENTITY列设置为主要键，你不能只删除一个主键 - 你需要先删除键，或先将另一列指定为PK。



但是..你确定要这么做吗？主键很重要 - 它们防止两个相同的行发生冲突并导致重大问题，并且通常充当FOREIGN KEY关系中两个表之间的链接字段。如果删除了IDENTITY列，很可能会损坏数据库的其余部分而无法修复。



不要删除它，尝试创建一个没有它的新表，并复制所有记录。如果可行，则没有重复项。您现在可以归档旧表，删除它，并重命名新。如果这一切都有效，那么你也没有外键问题。

但是在删除主键列之前，我会想很久很难！

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!

这篇关于如何删除SQL表中的标识列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！