CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-design
user5648283
user5648283

Reputation: 6213

Why am I getting "There is already an object named ... in the database"?

My code is

CREATE DATABASE JsPracticeDb; 

/* Create tables corresponding to the problems, solutions to 
   problems, and ratings of problems or solutions */
CREATE TABLE Problems 
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
    prompt_code VARCHAR(5000) NOT NULL,
    test_func_code VARCHAR(5000) NOT NULL,
    test_input_code VARCHAR(5000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Solutions  
(
   id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
   problem_id INT NOT NULL,
   solver VARCHAR(50),
   code VARCHAR(5000),
   FOREIGN KEY (problem_id) REFERENCES Problems(id) ON DELETE CASCADE,
   created DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Ratings 
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    stars TINYINT NOT NULL,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);

CREATE TABLE Comments 
(
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    commenter VARCHAR(50),
    comment VARCHAR(2000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);

/* Closure Table for comment hierarchy */
CREATE TABLE CommentPaths 
(
    ancestor INT NOT NULL,
    descendant INT NOT NULL,
    PRIMARY KEY (ancestor, descendant),
    FOREIGN KEY (ancestor) REFERENCES Comments(id) ON DELETE CASCADE,
    FOREIGN KEY (descendant) REFERENCES Comments(id) ON DELETE CASCADE
);
GO

/* Create trigger for deleting all comment descendants when 
   the comment is deleted */
CREATE TRIGGER deleteDescendants ON CommentPaths
FOR DELETE
AS
    DELETE FROM Comments 
    WHERE id IN (SELECT deleted.descendant FROM deleted);

and I'm getting

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

Msg 8197, Level 16, State 4, Procedure deleteDescendants, Line 45
The object 'CommentPaths' does not exist or is invalid for this operation.

as errors. There is not already an object named Problems, so I don't know why this thinks there is. In addition, why does is say that CommentPaths does not exist (Shouldn't it exist by the time the trigger is parsed)?

Upvotes: 1

Views: 1754

Answers (2)

Chiragkumar Thakar
Chiragkumar Thakar

Reputation: 3726

It shows an error because table Problems is existed in the database you are executing your Script, Try below solution

CREATE DATABASE JsPracticeDb; 
/* Create tables corresponding to the problems, solutions to 
   problems, and ratings of problems or solutions */
GO
USE[JsPracticeDb]
Go
CREATE TABLE Problems ( 
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
    prompt_code VARCHAR(5000) NOT NULL,
    test_func_code VARCHAR(5000) NOT NULL,
    test_input_code VARCHAR(5000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Solutions (
   id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
   problem_id INT NOT NULL,
   solver VARCHAR(50),
   code VARCHAR(5000),
   FOREIGN KEY (problem_id) REFERENCES Problems(id) ON DELETE CASCADE,
   created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Ratings (
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    stars TINYINT NOT NULL,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);
CREATE TABLE Comments ( 
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    commenter VARCHAR(50),
    comment VARCHAR(2000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);
/* Closure Table for comment hierarchy */
CREATE TABLE CommentPaths (
    ancestor INT NOT NULL,
    descendant INT NOT NULL,
    PRIMARY KEY (ancestor, descendant),
    FOREIGN KEY (ancestor) REFERENCES Comments(id) ON DELETE CASCADE,
    FOREIGN KEY (descendant) REFERENCES Comments(id) ON DELETE CASCADE
);
/* Create trigger for deleting all comment descendants when 
   the comment is deleted */
GO
CREATE TRIGGER deleteDescendants ON CommentPaths
FOR DELETE
AS
    DELETE FROM Comments 
    WHERE id IN (SELECT deleted.descendant FROM deleted);

This will solve your problem.

Upvotes: 1

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31203

After creating a database you have to switch to it using USE, otherwise the commands are sent to the current database, not the newly created one.

Upvotes: 4

Related Questions