Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

"The multi-part identifier ... could not be found"

  1. Jan 14, 2016 #1
    Microsoft SQL Server 2014 (T-SQL)

    My codez are

    Code (Text):

    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(3000) NOT NULL,
        test_func_code VARCHAR(3000) NOT NULL,
        test_input_code VARCHAR(3000) 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(3000),
       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=deleted.descendant;
     
    and the very last part deleted.descendant is getting hit with the error

    "The multi-part identifier deleted.descendant could not be bound."

    Any help?

    Also, is there anything wrong with the logic of my database setup, and there is any way of doing what I'm trying to do without using a trigger?
     
  2. jcsd
  3. Jan 14, 2016 #2

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    In a delete trigger, the virtual table "deleted" can contain more than one record, that's why
    Code (Text):

    DELETE FROM Comments WHERE id=deleted.descendant;
     
    doesn't work.
    Try something like
    Code (SQL):

    DELETE FROM Comments WHERE id IN (SELECT deleted.descendant FROM deleted);
     
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook