如何修复"Could not drop object 'Teams' because it is referenced by a FOREIGN KEY constraint"

Question

我的表"国家"有同样的错误，我通过先删除子表，然后是父母，然后找到解决方案。现在，我添加了更多的桌子，我试图复制我以前的工作，但没有任何工作。我认为这是因为团队和用户有外国钥匙相互引用。在底部，我更改了用户表以添加参考团队的外国密钥，但是即使我要删除那几行，因此它不会互相引用，但在执行它时，它也会给我相同的错误。这些错误是针对餐桌团队，用户和运动赛车的。运动习惯是父桌。

/*IF OBJECT_ID('dbo.Countries', 'U') IS NOT NULL 
DROP TABLE dbo.Countries;
IF OBJECT_ID('dbo.States', 'U') IS NOT NULL 
DROP TABLE dbo.States;
IF OBJECT_ID('dbo.Cities', 'U') IS NOT NULL 
DROP TABLE dbo.Cities;
IF OBJECT_ID('dbo.Venues', 'U') IS NOT NULL 
DROP TABLE dbo.Venues;
*/

DROP TABLE IF EXISTS Events
DROP TABLE IF EXISTS TeamMembers
DROP TABLE IF EXISTS EventAttendees
DROP TABLE IF EXISTS Teams
DROP TABLE IF EXISTS Users
DROP TABLE IF EXISTS ExerciseFoci 
DROP TABLE IF EXISTS Venues 
DROP TABLE IF EXISTS Cities
DROP TABLE IF EXISTS States
DROP TABLE IF EXISTS Countries

CREATE TABLE Countries (
countryCode varchar(5) NOT NULL,
countryName varchar(128) NOT NULL PRIMARY KEY
);
INSERT into Countries values
('US', 'United States');

CREATE TABLE States (
stateCode char(2) NOT NULL,
stateName varchar(128) NOT NULL PRIMARY KEY, 
countryName varchar(128) NOT NULL FOREIGN KEY REFERENCES 
Countries(countryName)
);
INSERT into States values 
('NJ', 'New Jersey', 'United States');

CREATE TABLE Cities (
cityName varchar(128) NOT NULL PRIMARY KEY,
stateName varchar(128) NOT NULL FOREIGN KEY REFERENCES States(stateName),
countryName varchar(128) NOT NULL FOREIGN KEY REFERENCES 
Countries(countryName)
)
INSERT into Cities values
('Newark', 'New Jersey', 'United States');

CREATE TABLE Venues (
venue varchar(128) NOT NULL PRIMARY KEY,
cityName varchar(128) NOT NULL FOREIGN KEY REFERENCES Cities(cityName),
stateName varchar(128) NOT NULL FOREIGN KEY REFERENCES States(stateName),
countryName varchar(128) NOT NULL FOREIGN KEY REFERENCES 
Countries(countryName)
)
INSERT into Venues values
('Football Field', 'Newark', 'New Jersey', 'United States');

CREATE TABLE ExerciseFoci(
name    varchar(50) PRIMARY KEY,
exercise_type   varchar(50),
primarily_outdoors bit
)
CREATE TABLE Users ( 
username    varchar(25) PRIMARY KEY,
password    varchar(25),
first_name  varchar(25),
last_name   varchar(25),
age         int,
city        varchar(128) REFERENCES Cities,
state       varchar(128) REFERENCES States,
country     varchar(128) REFERENCES Countries,
exercise_focus1 varchar(50) FOREIGN KEY references ExerciseFoci(name),
exercise_focus2 varchar(50) FOREIGN KEY references ExerciseFoci(name),
exercise_focus3 varchar(50) FOREIGN KEY references ExerciseFoci(name)
)
CREATE TABLE Teams(
name    varchar(50) PRIMARY KEY,
city        varchar(128) REFERENCES Cities,
state       varchar(128) REFERENCES States,
country     varchar(128) REFERENCES Countries,
admin1  varchar(25) FOREIGN KEY references Users(username),
admin2  varchar(25) FOREIGN KEY references Users(username),
admin3  varchar(25) FOREIGN KEY references Users(username),
exercise_focus1 varchar(50) FOREIGN KEY references ExerciseFoci(name),
exercise_focus2 varchar(50) FOREIGN KEY references ExerciseFoci(name),
exercise_focus3 varchar(50) FOREIGN KEY references ExerciseFoci(name)
)
CREATE TABLE Events(
name        varchar(50) PRIMARY KEY,
time        timestamp,
exercise_focus  varchar(50) FOREIGN KEY references ExerciseFoci,
venue       varchar(128) REFERENCES Venues,
city        varchar(128) REFERENCES Cities,
state       varchar(128) REFERENCES States,
country     varchar(128) REFERENCES Countries
)
CREATE TABLE EventAttendees(
username    varchar(25) FOREIGN KEY references Users(username),
team    varchar(50) FOREIGN KEY references Teams(name)
)
CREATE TABLE TeamMembers(
username    varchar(25) FOREIGN KEY references Users(username),
team    varchar(50) FOREIGN KEY references Teams(name)
)
ALTER TABLE Users
    ADD affiliated_team1 varchar(50) FOREIGN KEY references Teams(name);
ALTER TABLE Users
    ADD affiliated_team2 varchar(50) FOREIGN KEY references Teams(name);
ALTER TABLE Users
    ADD affiliated_team3 varchar(50) FOREIGN KEY references Teams(name); 

Answer 1

您必须先删除约束，然后再放下与约束表有关系的表。

您可以使用此查询在特定表上获得所有相反：

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('YourObj')

并使用以下查询生成滴剂或手动写入（或Excel）：

SELECT 
    'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('YourObj')