Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: Temporarily dropping constraints

    Assuming that I have created relationships (PKs and FKs) on my tables already, does the following statement permanently remove a Foreign Key constraint, or does it mearly disable it?

    Code:
    ALTER TABLE myTable NOCHECK CONSTRAINT FK_myForeignKey
    GO
    Also,
    I can't seem to find out how to temporarily remove the Identity qualifier of a field, and then reset it back as Identity later. Any help?

    Finally, will a failure of ALTER TABLE affect the @@ERROR variable? Can I check @@ERROR after each ALTER TABLE table statement to see if @@ERROR <> 0?

    The reason for both of these issues is that I am redesigning an unnormalized database, and I need to write a large script to drop all constraints on all tables, transform the data as normalized into the new table structure, and then re-enable constraints, Identity fields, etc. Thanks.
    Last edited by HardCode; 04-27-07 at 10:26.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    According to BOL, that should only disable the foreign key constraint, but when you re-enable the constraint, it does not check the foreign key relationship for consistency. You may end up with foreign key violations, if a "bad" record is updated later down the line.

    Look up SET IDENTITY_INSERT tablename ON. This will allow you to insert identity values into one table at a time.

    Code:
    create table test1
    (col1 int)
    
    insert into test1 values (1)
    insert into test1 values (1)
    
    alter table test1 add constraint pk primary key (col1)
    
    select @@error
    drop table test1

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    that's where DBCC CHECKCONSTRAINTS <table> ALL_CONSTRAINTS comes in handy.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    That will help. Thanks guys!
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •