Post reply

Add on delete cascada after table is already created

  • July 19, 2010 at 11:16 am

    #224985

    Hi, I have a table with a foreign key, when I created it I did not specified the ON DELETE CASCADE option.

    Now I'm trying to add it, but I just don't know how to do it, I already have data on that table so I don't want to recreate it.

    Here is what I used to create the table:

    CREATE TABLE division (

    Did int IDENTITY(1,1) NOT NULL,

    division varchar(50) NOT NULL,

    PRIMARY KEY (Did),

    proveedor_Pid int REFERENCES proveedor(Pid)

    )

    I want to add the ON DELETE CASCADE option without having to drop this table.

    Thanks in advance.

  • July 19, 2010 at 12:00 pm

    #1195042

    You do not need to rebuild the table, you simply need to rebuild the foreign key. DROP/CREATE the foreign key.

    Tara Kizer
    Microsoft MVP for Windows Server System - SQL Server
    Ramblings of a DBA (My SQL Server Blog)[/url]
    Subscribe to my blog

  • July 19, 2010 at 12:03 pm

    #1195044

    The thing is that I have no idea how to delete the constraint, because the code to drop foreign key does not work, I have to specify the foreign key name, but look at my table creation code and I did not specify anything.

    CREATE TABLE division (

    Did int IDENTITY(1,1) NOT NULL,

    division varchar(50) NOT NULL,

    PRIMARY KEY (Did),

    proveedor_Pid int REFERENCES proveedor(Pid)

    )

    thanks

  • July 19, 2010 at 12:07 pm

    #1195047

    When you don't specify a name for the fk constraint, SQL Server will generate one for you.

    Executesp_help 'division'to get the name of the fk constraint and the drop and add the constraint again with your correct cascade settings.

  • July 19, 2010 at 12:19 pm

    #1195056

    Thanks Everyone

    Now it is working.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply