locked
Foriegn key cascade delete RRS feed

  • Question

  • Hello all.

    I have a table called dbo.table_1 with a primary key on student_id and a second table called dbo.table_2, in this table student_id is not the Pk but idnum (idnum is defined as Int identity 1,1) is, since a student can be in table 2 multiple times.

    Im trying to write a statement that will cause all instances of a student id to be deleted in table_2 once it is deleted in table_1. bearing in mind studentid is not a pk. my statement so far which errors out.

    Thank you

    ALTER TABLE dbo.table_1 WITH NOCHECK ADD CONSTRAINT FK_studentln FOREIGN KEY(student_id)

    REFERENCES dbo.table_2(student_id )ON DELETE CASCADE

    GO

    Friday, November 2, 2012 3:49 PM

Answers

  • >> I -have a table called Table_1 with a PRIMARY KEY on student_id and a second table called Table_2, in this table student_id is not the PRIMARY KEY  but idnum (idnum is defined as INTEGER IDENTITY (1,1) is, since a student can be in table 2 multiple times. <<

    Thanks for a vague, useless narrative description of the schema. Polite people post DDL. Do you have to code from such things at your job?  Do you really use table names like “table_1”, etc? Oh, the ISO-111799 standard do not allow “id_num” as a data element name. An element can be “<something>_id” or a “<something>_num” because those postfixes are attribute properties. What you have is a chain of adjective looking for a noun. 

    But narrative is the wrong design! IDENTITY is a proprietary, non-relational count of physical insertion attempts that has nothing to with logical data model. Welcome back to magnetic tape serial file processing! 

    >> I'm trying to write a statement that will cause all instances of a student id to be deleted in table_2 once it is deleted in table_1. bearing in mind student_id is not a PRIMARY KEY. <<

    What is the problem? 

    CREATE TABLE Students
    (student_id CHAR(10) NOT NULL PRIMARY KEY,
      ..); 

    CREATE TABLE Nameless_Crud
    (international_crud_nbr CHAR(15) NOT NULL PRIMARY KEY,
     student_id CHAR(10) NOT NULL 
     REFERENCES Students(student_id)
     ON CASCADE DELETE,
     ...); 

    Please, please read any book on SQL. The table in which the attribute appears as a UNIQUE column set or PRIMARY KEY is the referenced table; the table in which the attribute appears as a FOREIGN KEY is the referencing table. The referenced and referencing tables can be the same. You have it inside out! 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Friday, November 2, 2012 8:28 PM
    • Marked as answer by Kalman Toth Sunday, November 11, 2012 7:15 PM
    Friday, November 2, 2012 8:26 PM

All replies

  • You should add the constraint to the table where the Student_id field is not the PK (e.g. to the child table). Also, you need to activate the constraint.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, November 2, 2012 4:36 PM
  • >> I -have a table called Table_1 with a PRIMARY KEY on student_id and a second table called Table_2, in this table student_id is not the PRIMARY KEY  but idnum (idnum is defined as INTEGER IDENTITY (1,1) is, since a student can be in table 2 multiple times. <<

    Thanks for a vague, useless narrative description of the schema. Polite people post DDL. Do you have to code from such things at your job?  Do you really use table names like “table_1”, etc? Oh, the ISO-111799 standard do not allow “id_num” as a data element name. An element can be “<something>_id” or a “<something>_num” because those postfixes are attribute properties. What you have is a chain of adjective looking for a noun. 

    But narrative is the wrong design! IDENTITY is a proprietary, non-relational count of physical insertion attempts that has nothing to with logical data model. Welcome back to magnetic tape serial file processing! 

    >> I'm trying to write a statement that will cause all instances of a student id to be deleted in table_2 once it is deleted in table_1. bearing in mind student_id is not a PRIMARY KEY. <<

    What is the problem? 

    CREATE TABLE Students
    (student_id CHAR(10) NOT NULL PRIMARY KEY,
      ..); 

    CREATE TABLE Nameless_Crud
    (international_crud_nbr CHAR(15) NOT NULL PRIMARY KEY,
     student_id CHAR(10) NOT NULL 
     REFERENCES Students(student_id)
     ON CASCADE DELETE,
     ...); 

    Please, please read any book on SQL. The table in which the attribute appears as a UNIQUE column set or PRIMARY KEY is the referenced table; the table in which the attribute appears as a FOREIGN KEY is the referencing table. The referenced and referencing tables can be the same. You have it inside out! 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Friday, November 2, 2012 8:28 PM
    • Marked as answer by Kalman Toth Sunday, November 11, 2012 7:15 PM
    Friday, November 2, 2012 8:26 PM