It is difficult to code in Microsoft SQL Server due to foreign keys and constraints

Question

There are a few questions I have regarding SQL. One issue I deal with is dyslexia, which causes me to mix up certain characters. Can I make things much larger in Azure Data Studio? In addition, I am not sure why I am receiving errors with my code. Below is the error message I am receiving. I have been researching foreign keys but I have not been able to resolve the issue.

Here is the code:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Director') 
    DROP TABLE Director

CREATE TABLE Director 
(
    DirectorlD int IDENTITY(1,1) NOT NULL, 
    Director_FirstName varchar(20), 
    Director_LastName varchar(25), 
    CONSTRAINT Director_PK PRIMARY KEY (DirectorlD)
)

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Star') 
    DROP TABLE Star

CREATE TABLE Star 
( 
    StarlD int IDENTITY(1,1) NOT NULL,
    Star_FirstName varchar(20), 
    Star_LastName varchar(20), 
    CONSTRAINT Star_PK PRIMARY KEY (StarlD) 
)

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Genre') 
    DROP TABLE Genre

CREATE TABLE Genre 
(
    GenrelD int IDENTITY(1,1) NOT NULL, 
    Genre varchar(28), 
    CONSTRAINT Genre_PK PRIMARY KEY (GenrelD)
)

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Films') 
    DROP TABLE Films

CREATE TABLE Films  
(
    FilmslD int IDENTITY(1,1) NOT NULL, 
    Title varchar(49), 
    Rating NUMERIC(3,1), 
    DirectorID int, 
    StarlD int, 
    GenrelD int, 
    CONSTRAINT FilmslD_PK PRIMARY KEY (FilmslD)
)

ALTER TABLE Films 
    ADD CONSTRAINT fkl 
        FOREIGN key(DirectorID) REFERENCES Director(DirectorID);

ALTER TABLE Films 
    ADD CONSTRAINT fk2 
        FOREIGN key(StarlD) REFERENCES Star(StarlD); 

ALTER TABLE Films 
    ADD CONSTRAINT fk3 
        FOREIGN key(GenrelD) REFERENCES Genre(GenrelD);

Error:

Msg 2714, Level 16, State 6, Line 6
There is already an object named 'Director' in the database.

Msg 3726, Level 16, State 1, Line 4
Could not drop object 'Director' because it is referenced by a FOREIGN KEY constraint.

My code part 1

My code part 2

dropping should be in reverse order. First drop child tables and then drop parent table. If you drop parent first and if there is any FK constraint then it will throw error. — Amit Verma, Aug 24 '22 at 04:06
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Aug 24 '22 at 04:24
To make things larger, you can just hold CTRL and spin your mouse wheel. — Charlieface, Aug 24 '22 at 05:42
Move all the `IF... DROP` statements to the top, and move the drop for child table `Film` to the the beginning — Charlieface, Aug 24 '22 at 05:44

score 0 · Answer 1 · answered Aug 24 '22 at 10:32

Could not drop object 'Director' because it is referenced by a FOREIGN KEY constraint.

From the above error we know that the table ' Director' is referenced by Foreign Key Constraint. first, we need to drop that foreign keys then drop the table.

With help of following query, you can get foreign key constraint names and the referencing table names

SELECT name AS 'Foreign Key Constraint Name', 
OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table' 
FROM sys.foreign_keys 
WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'schemaname' AND OBJECT_NAME(referenced_object_id) = 'tablename'

Now you can alter the child table and drop the constraint

ALTER TABLE childtable DROP CONSTRAINT fkconstraint;

After dropping all foreign key constraint for table, you can now drop the table by

Drop table tablename

Also refer this SO Thread answer by marc_s

It is difficult to code in Microsoft SQL Server due to foreign keys and constraints

1 Answers1