Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: droping a CONSTRAINT

    I am having problem to find the right syntax to DROP a column with contrainst and recrate it
    I get an error

    if exists ( select * from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME='myTable'
    and COLUMN_NAME='myDate' )
    ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
    GO

    ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
    myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
    GO

    Query Analyser says :
    Server: Msg 5074, Level 16, State 1, Line 5
    The object 'DF_myDate' is dependent on column 'myDate'.
    Server: Msg 4922, Level 16, State 1, Line 5
    ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
    Server: Msg 2705, Level 16, State 4, Line 2
    Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.

    thank you for helping

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You do know that the first statement failed, and since it was isolated in it's own batch by the GO, the second statement tried to run. So that's uderstandable, since the column did not drop, you can't re-add it.

    Do this, go in to Enterprise Manager, right click on the table and chose design make your changes, DON'T SAVE them, and click on the save script icon

    It will show you exactly what to do
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    I get an incredible script !!

    I really dont understant
    here I dop the column

    if exists ( select * from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME='myTable'
    and COLUMN_NAME='myDate' )
    ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
    GO

    then it doesnt exist any more ! why I cannot create it after ?
    Last edited by quentin; 11-30-05 at 23:18.

  4. #4
    Join Date
    Dec 2003
    Posts
    39
    because u failed to drop it......... u got error message :
    Server: Msg 5074, Level 16, State 1, Line 5
    The object 'DF_myDate' is dependent on column 'myDate'.
    Server: Msg 4922, Level 16, State 1, Line 5
    ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.

    u cant drop the column, coz it has constraint 'DF_myDate'. drop the constraint first, then drop the column, then create the column with constraint again.
    Last edited by Jelly Link; 12-01-05 at 00:43.
    Link Link

Posting Permissions

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