Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Stuck in SProcs 'n' Constraints

Back to Forum: Tech Off
  • User profile image
    W3bbo

    'lo

    I've got two applicable tables:

    Articles(ID bigint identity, CurrentRevision bigint Not Null)
    ArticleRevisions(ID bigint identity, InArticle bigint Not Null, Body ntext Not Null)

    There is a constraint between (Articles.CurrentRevision and ArticleRevisions.ID) and (ArticleRevisions.InArticle and Articles.ID)

    So question:

    How do I insert into Articles and ArticleRevisions in a "parallel way" which prevents the constraints coming into force?

    Sven suggests I make CurrentRevision nullable, are there any alternative methods?

    Also, what does SQL Server 2005 do to help this conundrum?

    Danké

  • User profile image
    Lee_Dale

    ALTER TABLE <tablename> NOCHECK CONSTRAINT ALL

  • User profile image
    Maurits

    W3bbo wrote:
    Sven suggests I make CurrentRevision nullable


    I concur.  If CurrentRevision is nullable, you can insert into Articles first, insert into ArticleRevisions second, and finally update Articles.

    Or you could make InArticle nullable; insert into ArticleRevisions first; insert into Articles second; and finally update ArticleRevisions.

    Or, as suggested by leeappdalecom, you could programmatically "turn off" referential integrity during the sproc.

  • User profile image
    thepuffin

    Refactor your database so that Revisions has an IsCurrent bit in it instead of the CurrentRevision int on Articles. You could then create a view that has the current one if you really need it.

  • User profile image
    thepuffin

    Or... Three tables, Articles { Id, CurrentRevision } , Revisions { Id }, ArticleRevisions { Article.Id, Revisions.Id }. Then insert Revision, insert Articles, Insert ArticleRevisions.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.