Argthtjtr

Below is the code for the entire script. the only issue i have is with the FK below (see lines with -- in front) to remove it. if I add that I get an error using a DELETE, if I only apply the 4 constraints instead of 5 I can delete from the Artist table and it deletes from the other 3 as it should. but when I add PersonalInfo with an ON DELETE CASCADE suddenly I get an error, why? I plan to remove AlbumID from all tables so that they all rely on ArtistID to be identified. Allowing me to use the same column for FK as 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

Because you are missing "ON DELETE CASCADE" here

--ALTER TABLE PersonalInfo

--ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID) <---- Missing on delete cascade

--GO

Once you add that

ALTER TABLE PersonalInfo

ADD FOREIGN KEY (ArtistID) REFERENCES Artists(ArtistID)

ON DELETE CASCADE

GO

it works as required