Post reply

Problem with delete cascade and identity column in sql 2005

  • July 24, 2011 at 9:19 pm

    #241989

    Hello everybody!

    I have a problem with delete cascade and identity column in sql 2005.

    I have 3 tables as following:

    Table 1:

    CREATE TABLE Person

    (

    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    Last varchar(18) NOT NULL,

    First varchar(14) NOT NULL

    )

    Table 2:

    CREATE TABLE Account

    (

    Acctno int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    Id int NOT NULL,

    [Type] varchar(12) NOT NULL,

    AdmitDate Datetime NOT NULL,

    DCDate Datetime NULL,

    CONSTRAINT [FK_Account_Person] FOREIGN KEY([Id])

    REFERENCES Person(Id)

    ON UPDATE CASCADE

    ON DELETE CASCADE

    )

    Table 3:

    CREATE TABLE Order

    (

    Orderno int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    AcctNo Int NOT NULL,

    StartDate Datetime NOT NULL,

    DCDate Datetime NULL,

    CONSTRAINT [FK_Order_Account] FOREIGN KEY([Acctno])

    REFERENCES Account(Acctno)

    ON UPDATE CASCADE

    ON DELETE CASCADE

    )

    After that I Create a procedure as following:

    CREATEPROCEDURE [dbo].[DeleteTable]

    (

    @Tblname VARCHAR(100)

    )

    AS

    DECLARE @strquery VARCHAR(max)

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SET @strquery = 'DELETE FROM [' + @tblname +']'

    EXEC (@strquery)

    IF EXISTS(SELECT syscolumns.name FROM syscolumns WHEREOBJECT_NAME(id) = @tblname and COLUMNPROPERTY(id, name,'IsIdentity')=1)

    DBCC CHECKIDENT(@Tblname, RESEED, 0)

    END

    When I EXEC this procedure with @Tblname='Person', all records in 3 these tables will be deleted immediately and identity of column Person.Id=0. However, 2 identity columns in 2 table Account and Order are not reset. How to reset identity of all child tables when deleting parent table?

    Please help me to solve it!

    Thanks so much.

  • July 25, 2011 at 5:16 am

    #1357688

    There's no way it would have made sense to reseed all 3 tables based on your current code (MS point of view).

    Why do you want to resuse the identity #? I've rarely seen that as actually required by the business. And if it is so then you have to do that yourself.

  • July 25, 2011 at 5:29 am

    #1357693

    The idea of an identity column per table is to uniquely identify a row in a table. The scope of this identity value is the table the column belongs to but not a list of tables.

    It doesn't matter if the identity values for different tables are in sync or not.

    What is the goal you're trying to achieve?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 25, 2011 at 6:59 pm

    #1358214

    My goal is able to get a database which have empty tables like as beginning without using file bak.

  • July 25, 2011 at 7:01 pm

    #1358215

    goodtomorrow01 (7/25/2011)

    My goal is able to get a database which have empty tables like as beginning without using file bak.

    Truncate the target tables, working from the bottom up in your foreign key chain.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 25, 2011 at 7:11 pm

    #1358219

    I think Sql can delete all data of child tables when a parent table is deleted cascade then why there is no way to reset identity of child tables. How to know how many child tables are deleted data and what their name are when a parent table is deleted?

  • July 26, 2011 at 5:14 am

    #1358368

    I'd go the other way around. Script all objects and recreate the db from scratch. Then maybe import the lookup tables (Types, states, countries, etc).

    You cannot use truncate on a table with FK on it. So you'd have to use delete + reseed on all tables. That would get annoying realy fast not to mention all the useless log operations!

  • July 26, 2011 at 11:27 am

    #1358690

    Ninja's_RGR'us (7/26/2011)

    You can['t] use truncate on a table with FK on it. So you'd have to use delete + reseed on all tables. That would get annoying realy fast not to mention all the useless log operations!

    You're right Remi, sorry, I missed the enforced references in the schema. Thanks for catching that.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 26, 2011 at 11:34 am

    #1358694

    Craig Farrell (7/26/2011)

    Ninja's_RGR'us (7/26/2011)

    You can['t] use truncate on a table with FK on it. So you'd have to use delete + reseed on all tables. That would get annoying realy fast not to mention all the useless log operations!

    You're right Remi, sorry, I missed the enforced references in the schema. Thanks for catching that.

    Thanks for reading my mind... edited my post to canNOT. 😉

  • July 28, 2011 at 3:35 am

    #1359885

    Maybe I must reset it manually. Thanks so much for taking part in reply my question.

  • July 28, 2011 at 5:28 am

    #1359946

    goodtomorrow01 (7/28/2011)

    Maybe I must reset it manually. Thanks so much for taking part in reply my question.

    What part of script all objects and rebuild the db from scratch didn't you like or understand?

Viewing 11 posts - 1 through 10 (of 10 total)

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