如何更改表复合主键

CREATE TABLE [dbo].[INVS_ITEM_LOCATIONS]
([DEPARTMENT_CODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [IM_INV_NO] [numeric](10, 0) NOT NULL,
 [LOCATION_CODE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [CURR_QTY] [numeric](10, 0) NOT NULL CONSTRAINT [DF__INVS_ITEM__CURR___1352D76D]  DEFAULT ((0)),
 [DO_QTY] [numeric](10, 0) NOT NULL CONSTRAINT [DF__INVS_ITEM__DO_QT__1446FBA6]  DEFAULT ((0)),
 [ALLOC_QTY] [numeric](10, 0) NOT NULL CONSTRAINT [DF__INVS_ITEM__ALLOC__153B1FDF]  DEFAULT ((0)),
 [YOB_QTY] [numeric](10, 0) NOT NULL CONSTRAINT [DF__INVS_ITEM__YOB_Q__162F4418]  DEFAULT ((0)),
 [FOC_QTY] [numeric](10, 0) NULL CONSTRAINT [DF__INVS_ITEM__FOC_Q__17236851]  DEFAULT ((0)),
 [USER_CREATED] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [DATE_CREATED] [datetime] NOT NULL,
 [USER_MODIFIED] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [DATE_MODIFIED] [datetime] NULL,

 CONSTRAINT [INVS_ITEM_LOCATIONS_PK] 
 PRIMARY KEY CLUSTERED ([DEPARTMENT_CODE] ASC,
 [IM_INV_NO] ASC, [LOCATION_CODE] ASC)
 WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

This is my table structure ......how can I remove the composite primary key in table and also I should add foreign key to im_inv_no reference table is invs_location which contain im_inv_no and the department_code should be same primary key .pls help

这是我的表结构......如何删除表中的复合主键，我还应该将外键添加到im_inv_no引用表是包含im_inv_no的invs_location，而department_code应该是相同的主键.pls help

2 个解决方案

#1

To remove your composite primary key, use:

要删除复合主键，请使用：

ALTER TABLE dbo.INVS_ITEM_LOCATIONS
  DROP CONSTRAINT INVS_ITEM_LOCATIONS_PK

To add a foreign key, use this:

要添加外键，请使用以下命令：

ALTER TABLE dbo.INVS_ITEM_LOCATIONS
  ADD CONSTRAINT FK_INV_NO_REFERENCE
  FOREIGN KEY(IM_INV_NO, DEPARTMENT_CODE)
  REFERENCES dbo.invs_location(IM_INV_NO, DEPARTMENT_CODE)

These are all really basic beginner SQL questions - I would strongly recommend you read one of the various good SQL tutorials out there to get used to SQL first, before posting each and every single little question here....

这些都是非常基本的初学者SQL问题 - 我强烈建议你先阅读各种优秀的SQL教程之一，先熟悉SQL，然后再发布每一个小问题....

W3Schools SQL Tutorial

W3Schools SQL教程

Marc

渣子

#2

You can create new tables with modifications you need, copy your data then renaming new talbes to the same names as old tables and deleting old ones. It is probably the most efficient way as well.

您可以创建需要修改的新表，复制数据，然后将新塔重命名为与旧表相同的名称，并删除旧表。它也许是最有效的方式。

#1