Table A's PK is being referenced by Table B's FK. Cannot drop Table A's PK

Question

MS SQL Server

I'm making a star schema. I've set the PK's and FK for my tables and am now trying to write a procedure that will drop the constraints, truncate the tables, add the constraints again, and then repopulate the tables. When I try to drop the constraints I receive the error:

"The constraint PK_TIMEDIM is being referenced by table SalesFactTable, foreign key constraint FK_SALESFACTTABLE. Could not drop constraint."

EDIT: One problem solved. Another found. I receive the same error except now there are mysterious, auto-generated FK's such as FK__SalesFact__CUST___19DFD96B.

Please show me what I'm doing wrong.

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)

Answer 1

**Foreign keys are referenced by the Primary key, so you are not allowed to remove the primary key before removing the Foreign Key constraint. so, you need to remove the foreign key first to remove the Primary key constraint from a table.

**

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)