none
can you make a key with delete cascade when there is no primary key?

    Question

  • I have a table with a primary key, and also a field called 'tpid', which is sometimes null, and sometimes has an integer value in it. This integer value corresponds a primary key in another table, and that key is also called tpid.

    I would like the following to happen.

    If a row in table 1 is deleted, and it has a tpid that is not null, then I want the corresponding row in the second table to be deleted too.

    I think a trigger could be written to do this, but it would be better if a foreign key constraint with cascade delete could be used.

    One reason that is better is that whenever I script out my database, triggers seem to be left out by default, unless I specifically ask that they not be..

    So can this be done - where column in table 1 has some nulls, and is not a primary key, but when its deleted, you want to delete a matching row in a table where it IS a primary key.

    Thanks,

    Gideon

    Saturday, June 14, 2014 6:44 PM

Answers

  • Nope. ON DELETE CASCADE works opposite way ie when you delete a record in main table all references in child table gets deleted. In your case you want to propagate change to parent table so what you need is a trigger like below

    CREATE TRIGGER Trg_TableName
    ON yourTable
    AFTER DELETE
    AS
    BEGIN 
    
    DELETE t1
    FROM Table1 t1
    INNER JOIN DELETED d
    ON d.tpid = t1.tpid
    
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, June 14, 2014 6:59 PM
  • As you said, Script Trigger option is set to false by default for database scripting. CASCADE DELETE is not an option for you as well. Its better to change the option ONLY ONCE at the time of scripting, it would take care of for all the objects. It should not be a worrisome. 

    If you know the places where the delete happens, then you can delete from the parent table in the same procedure itself, if thats feasible in your case. But if you are not sure of the places(procs) where delete happens, then you may go with TRIGGER.

    • Marked as answer by Gideon Isaac Sunday, June 15, 2014 8:04 PM
    Saturday, June 14, 2014 7:24 PM

All replies

  • Nope. ON DELETE CASCADE works opposite way ie when you delete a record in main table all references in child table gets deleted. In your case you want to propagate change to parent table so what you need is a trigger like below

    CREATE TRIGGER Trg_TableName
    ON yourTable
    AFTER DELETE
    AS
    BEGIN 
    
    DELETE t1
    FROM Table1 t1
    INNER JOIN DELETED d
    ON d.tpid = t1.tpid
    
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, June 14, 2014 6:59 PM
  • As you said, Script Trigger option is set to false by default for database scripting. CASCADE DELETE is not an option for you as well. Its better to change the option ONLY ONCE at the time of scripting, it would take care of for all the objects. It should not be a worrisome. 

    If you know the places where the delete happens, then you can delete from the parent table in the same procedure itself, if thats feasible in your case. But if you are not sure of the places(procs) where delete happens, then you may go with TRIGGER.

    • Marked as answer by Gideon Isaac Sunday, June 15, 2014 8:04 PM
    Saturday, June 14, 2014 7:24 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    >> I have a table with a PRIMARY KEY, <<

    That is redundant. Table has a key by definition, but the fact that you said it this way tells us you have a high level of ignorance. 

    >> .. and also a field [sic] called “tp_id”, which is sometimes NULL, and sometimes has an integer value in it. <<

    Yep! Wrong again! A column is not anything like a field. The only place “field” is use in SQL is for {year, month, day, hour, minute, second} in temporal values. 

    >> This integer value corresponds a PRIMARY KEY in another table, and that key is also called tp_id. <<

    NO, NO, NO! A key is never NULL by definition. It cannot be NULL in your un-named second table. Where is the DDL? Why do you have manners of a pig? 

    I will guess, since we have no clear specs from you, that you want to replace the NULL with a dummy value that can be used with DRI actions. 

    Would you like to give us enough information to help you? 

    --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

    Sunday, June 15, 2014 2:57 AM
  • All 3 of you gave me valuable information, but CELKO seems to think I have the manners of a pig.  How did he know!  I thought I had kept it a secret all these years.....
    Sunday, June 15, 2014 8:05 PM