SQL server 2017关于删除级联问题

Question

下面是整个脚本的代码。我唯一的问题是使用下面的FK(请参阅前面的--行(来删除它。如果我添加使用DELETE时出现错误，如果我只应用4个约束而不是5个约束，我可以从Artist表中删除，它会从其他3个约束中删除。但是当我用一个ON DELETE CASCADE添加PersonalInfo时，我突然出现了一个错误，为什么？我计划从所有表中删除AlbumID，以便它们都依赖于ArtistID进行识别。允许我对FK使用与PK.相同的列

CREATE TABLE Artists
( ArtistName varchar(20) NOT NULL, ArtistID varchar(20) NOT NULL, NumberOfAlbumTitles varchar(10) NOT NULL, AlbumID varchar(20) NOT NULL,
PRIMARY KEY (ArtistID))
GO
CREATE TABLE Sales
( AlbumID varchar(20) NOT NULL, CopiesSoldYTD varchar(20) NOT NULL, ArtistID varchar(20) NOT NULL, SalesTotal varchar(20) NOT NULL,
PRIMARY KEY (AlbumID))
GO
CREATE TABLE Production
( AlbumID varchar(20), Copies varchar(20) NOT NULL, UnitPrice varchar(10) NOT NULL, AlbumTitle varchar(20) NOT NULL, ArtistID varchar(20) NOT NULL,
PRIMARY KEY (ArtistID))
GO
CREATE TABLE PersonalInfo
( FirstName varchar(20) NOT NULL, LastName varchar(30) NOT NULL, HomeAddress varchar(30) NOT NULL, PhoneNumber varchar(10) NOT NULL, ArtistID varchar(20) NOT NULL,
PRIMARY KEY (ArtistID))
GO
CREATE TABLE PersonalInfo2
( City varchar(20) NOT NULL, LabelName varchar(20), PostalZip varchar(6) NOT NULL, Region varchar(30) NOT NULL, ArtistID varchar(20) NOT NULL,
PRIMARY KEY (ArtistID))
GO
INSERT INTO Artists 
VALUES ('Mr Roberts', 1, 4, 10),
('MC Boogie', 2, 3, 11),
('Singin Sam', 3, 1, 12),
('Avenger', 4, 2, 13)
GO
INSERT INTO Sales
VALUES (10, 232 , 1, 2320),
(11, 151, 2, 1510),
(12, 129, 3, 1290),
(13, 487, 4, 4870)
GO
INSERT INTO Production
VALUES (10 , 500 , 10, 'Roberts 1', 1),
(11, 700, 10, 'Time To Boogie', 2),
(12, 250, 10, ' Dance Dance Dance', 3),
(13, 1000, 10, 'The Revenge Of...',  4)
GO
INSERT INTO PersonalInfo
VALUES ('Brad', 'Roberts' , ' 126 Somewhere Lane', 2048888888, 1),
('Doug', 'Boogie', '234 East bay', 9078789090, 2),
('Raymond', 'Disco', ' 123 Dancing Queen Blvd', 3038761234, 3),
('Ryan', 'Apple', '66 Berkshire Bay', 4549091212, 4)
GO
INSERT INTO PersonalInfo2
VALUES ('Winnipeg', 'Ready Records', 'R2E9N8', 1, 1),
('Calgary','Set Records', 'R3J1M7', 2, 2),
('Texas', 'Go Records', '56555', 5, 3),
('London', 'Canadian Recordings','98887', 4, 4)
GO
ALTER TABLE Sales
ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)
ON DELETE CASCADE 
GO
--ALTER TABLE PersonalInfo
--ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)
--GO
ALTER TABLE PersonalInfo2
ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)
ON DELETE CASCADE
GO
ALTER TABLE Sales
ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)
GO
ALTER TABLE Production
ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)
ON DELETE CASCADE

GO
UPDATE Artists
SET ArtistName = 'Mr. Roberts'
WHERE ArtistID = 1
GO
UPDATE Production 
SET Copies = 589, UnitPrice = 12
WHERE AlbumID = 10
GO
UPDATE PersonalInfo
SET HomeAddress = '345 Pritchard Rd', PhoneNumber = 2042341234
WHERE ArtistID = 1
GO
CREATE INDEX index1
ON dbo.Artists (ArtistID, AlbumID);
GO
CREATE INDEX index3
ON Sales (AlbumID, ArtistID);
GO
CREATE INDEX index4
ON Production (AlbumID, ArtistID);
GO
CREATE INDEX index5
ON PersonalInfo2 (City, ArtistID);
GO
CREATE INDEX index6
ON PersonalInfo (ArtistID);
GO
CREATE INDEX index7
ON Artists (ArtistName, NumberOfAlbumTitles);
GO
CREATE INDEX index8
ON Production (ArtistID, AlbumID, Copies, UnitPrice);
GO
CREATE INDEX index9
ON Artists (AlbumID);
GO
CREATE INDEX index11
ON Sales (ArtistID);
GO
CREATE INDEX index12
ON Production (ArtistID)
GO
CREATE INDEX index13
ON PersonalInfo2 (ArtistID);
GO
CREATE VIEW view1 AS
SELECT FirstName, LastName, ArtistName, PhoneNumber, CopiesSoldYTD, SalesTotal
FROM PersonalInfo
INNER JOIN Sales
ON PersonalInfo.ArtistID = Sales.ArtistID
INNER JOIN Artists 
ON Sales.ArtistID = Artists.ArtistID
GO
CREATE PROCEDURE Proc1
AS
SELECT FirstName, LastName, Artists.ArtistName, NumberOfAlbumTitles, Artists.ArtistID, LabelName, PhoneNumber, City, UnitPrice, CopiesSoldYTD, SalesTotal
FROM Artists
LEFT JOIN PersonalInfo
ON Artists.ArtistID = PersonalInfo.ArtistID
LEFT JOIN PersonalInfo2
ON PersonalInfo.ArtistID = PersonalInfo2.ArtistID
INNER JOIN Production
ON PersonalInfo2.ArtistID = Production.ArtistID
INNER JOIN Sales
ON Production.ArtistID = Sales.ArtistID
GO
CREATE PROCEDURE dbo.FinalProjectErrorHandling2
AS
BEGIN TRY  
SELECT CopiesSoldYTD, SalesTotal
FROM Sales
GROUP BY SalesTotal, CopiesSoldYTD
HAVING CopiesSoldYTD > 200
END TRY  
BEGIN CATCH  
WHILE(ERROR_NUMBER() > 0 )
RAISERROR ('the error was handled',0,1) WITH NOWAIT
SELECT
ERROR_MESSAGE() AS ErrorMessage
END CATCH  
GO

Answer 1

因为此处缺少"删除级联">

--ALTER TABLE PersonalInfo
--ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID) <---- Missing on delete cascade
--GO

一旦你添加

ALTER TABLE PersonalInfo
ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)
ON DELETE CASCADE
GO

它按要求工作