MS SQL Server
我正在制作一个星形模式。我已经为我的表设置了PK's
和FK
,现在正在尝试编写一个过程,该过程将删除约束,截断表,再次添加约束,然后重新填充表。当我尝试删除约束时,我收到错误:
"约束PK_TIMEDIM
被表 SalesFactTable
引用,外键约束FK_SALESFACTTABLE
。无法放弃约束。
编辑:一个问题解决了。另一个发现。我收到同样的错误,除了现在有神秘的自动生成FK's
,例如FK__SalesFact__CUST___19DFD96B
。
请告诉我我做错了什么。
ALTER PROCEDURE [dbo].[A11]
AS
BEGIN
--Drop constraints
ALTER TABLE SalesFactTable
DROP CONSTRAINT FK_SALESFACTTABLE
ALTER TABLE SalesFactTable
DROP CONSTRAINT PK_SALESFACTTABLE
ALTER TABLE TimeDim
DROP CONSTRAINT PK_TIMEDIM
ALTER TABLE CustomerDim
DROP CONSTRAINT PK_CUSTOMERDIM
ALTER TABLE PartDim
DROP CONSTRAINT PK_PARTDIM
--Truncate tables
TRUNCATE TABLE TimeDim
TRUNCATE TABLE CustomerDim
TRUNCATE TABLE PartDim
TRUNCATE TABLE SalesFactTable
--Add constraints
ALTER TABLE TimeDim
ADD CONSTRAINT PK_TIMEDIM PRIMARY KEY (TIME_ID)
ALTER TABLE CustomerDim
ADD CONSTRAINT PK_CUSTOMERDIM PRIMARY KEY (CUST_ID)
ALTER TABLE PartDim
ADD CONSTRAINT PK_PARTDIM PRIMARY KEY (PART_ID)
ALTER TABLE SalesFactTable
ADD CONSTRAINT FK_SALESFACTTABLE FOREIGN KEY (TIME_ID) REFERENCES TimeDim (TIME_ID),
FOREIGN KEY (CUST_ID) REFERENCES CustomerDim (CUST_ID),
FOREIGN KEY (PART_ID) REFERENCES PartDim (PART_ID)
ALTER TABLE SalesFactTable
ADD CONSTRAINT PK_SALESFACTTABLE PRIMARY KEY (TIME_ID, CUST_ID, PART_ID)
**外键由主键引用,因此在删除外键约束之前不允许删除主键。因此,您需要先删除外键才能从表中删除主键约束。
**
ALTER PROCEDURE [dbo].[A11]
AS
BEGIN
--Drop FK constraints
ALTER TABLE SalesFactTable
DROP CONSTRAINT FK_SALESFACTTABLE
--Drop PK constraints
ALTER TABLE SalesFactTable
DROP CONSTRAINT PK_SALESFACTTABLE
ALTER TABLE TimeDim
DROP CONSTRAINT PK_TIMEDIM
ALTER TABLE CustomerDim
DROP CONSTRAINT PK_CUSTOMERDIM
ALTER TABLE PartDim
DROP CONSTRAINT PK_PARTDIM
--Truncate tables
TRUNCATE TABLE TimeDim
TRUNCATE TABLE CustomerDim
TRUNCATE TABLE PartDim
TRUNCATE TABLE SalesFactTable
--Add constraints
ALTER TABLE TimeDim
ADD CONSTRAINT PK_TIMEDIM PRIMARY KEY (TIME_ID)
ALTER TABLE CustomerDim
ADD CONSTRAINT PK_CUSTOMERDIM PRIMARY KEY (CUST_ID)
ALTER TABLE PartDim
ADD CONSTRAINT PK_PARTDIM PRIMARY KEY (PART_ID)
ALTER TABLE SalesFactTable
ADD CONSTRAINT FK_SALESFACTTABLE FOREIGN KEY (TIME_ID) REFERENCES TimeDim (TIME_ID),
FOREIGN KEY (CUST_ID) REFERENCES CustomerDim (CUST_ID),
FOREIGN KEY (PART_ID) REFERENCES PartDim (PART_ID)
ALTER TABLE SalesFactTable
ADD CONSTRAINT PK_SALESFACTTABLE PRIMARY KEY (TIME_ID, CUST_ID, PART_ID)