Tags: alter, commandexec, constraints, database, disable, disabled, dts, microsoft, mysql, nocheck, oracle, running, server, sp_msforeachtable, sql, table, thefollowing

disable constraints

On Database » Microsoft SQL Server

3,990 words with 5 Comments; publish: Tue, 06 May 2008 23:20:00 GMT; (25046.88, « »)

Hi,

I disabled all constraints on a database before using DTS by running the

following command:

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

When comparing the source tables and the destination tables after the DTS I

found that some DEFAULT constraints were not disabled. The result was that

source columns populated with NULLs got populated on the destinatin columns

with the dafult values as defined in the constraints.

Thanks,

Yaniv

All Comments

Leave a comment...

  • 5 Comments
    • You cannot disable DEFAULT, UNIQUE or PRIMARY KEY constraints.

      Tibor Karaszi, SQL Server MVP

      http://www.karaszi.com/sqlserver/default.asp

      http://www.solidqualitylearning.com/

      "Yaniv" <yanive.sqlserver.itags.org.rediffmail.com> wrote in message news:ePf1C8o7FHA.4076.sqlserver.itags.org.tk2msftngp13.phx.gbl

      ..

      > Hi,

      > I disabled all constraints on a database before using DTS by running the f

      ollowing command:

      > exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

      > When comparing the source tables and the destination tables after the DTS

      I found that some

      > DEFAULT constraints were not disabled. The result was that source columns

      populated with NULLs got

      > populated on the destinatin columns with the dafult values as defined in t

      he constraints.

      >

      > Thanks,

      > Yaniv

      >

      #1; Tue, 06 May 2008 23:21:00 GMT
    • Hi, Yaniv

      I think you should have first to drop contstraint (DEFAULT in your case) ,

      insert the data and then re-create contraints

      "Yaniv" <yanive.sqlserver.itags.org.rediffmail.com> wrote in message

      news:ePf1C8o7FHA.4076.sqlserver.itags.org.tk2msftngp13.phx.gbl...

      > Hi,

      > I disabled all constraints on a database before using DTS by running the

      > following command:

      > exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

      > When comparing the source tables and the destination tables after the DTS

      > I found that some DEFAULT constraints were not disabled. The result was

      > that source columns populated with NULLs got populated on the destinatin

      > columns with the dafult values as defined in the constraints.

      >

      > Thanks,

      > Yaniv

      >

      #2; Tue, 06 May 2008 23:22:00 GMT
    • Thank you all,

      This is what I did; I droped and recreated the defaults but I wanted to

      find out why a default constraint was not diabled.

      "Uri Dimant" <urid.sqlserver.itags.org.iscar.co.il> wrote in message

      news:OjQBmIp7FHA.3752.sqlserver.itags.org.tk2msftngp13.phx.gbl...

      > Hi, Yaniv

      > I think you should have first to drop contstraint (DEFAULT in your case)

      > , insert the data and then re-create contraints

      >

      >

      >

      >

      > "Yaniv" <yanive.sqlserver.itags.org.rediffmail.com> wrote in message

      > news:ePf1C8o7FHA.4076.sqlserver.itags.org.tk2msftngp13.phx.gbl...

      >

      #3; Tue, 06 May 2008 23:23:00 GMT
    • See my earlier reply. You cannot disable default constraints. I.e., default,

      pk and uq constraints

      are not disabled when you disable "ALL" constraints for a table.

      Tibor Karaszi, SQL Server MVP

      http://www.karaszi.com/sqlserver/default.asp

      http://www.solidqualitylearning.com/

      "Yaniv" <yanive.sqlserver.itags.org.rediffmail.com> wrote in message news:OPF1Tfp7FHA.3388.sqlserver.itags.org.TK2MSFTNGP11.phx.gbl

      ..

      > Thank you all,

      > This is what I did; I droped and recreated the defaults but I wanted to f

      ind out why a default

      > constraint was not diabled.

      >

      > --

      > "Uri Dimant" <urid.sqlserver.itags.org.iscar.co.il> wrote in message news:OjQBmIp7FHA.3752.sqlserver.itags.org.tk2

      msftngp13.phx.gbl...

      >

      #4; Tue, 06 May 2008 23:25:00 GMT
    • hi guys try the attached file to dynamically drop a default constraint on a

      column in a table.

      #5; Tue, 06 May 2008 23:25:00 GMT