drop constraint, problem
in [Programming SQL]
|
Prev: db mail
Next: SQL server message
From: Jeff on 14 Dec 2007 07:58 Hey Sql Server 2005 In a table I added an extra column to a table: alter table tableA add checked bit default 0 Then I give this column a value: update tableA set checked = 0; Now I create a stored procedure which contain 3 select using the column 'checked' in the where clause. after running the stored procedure once, then I want to remove that extra column I added to the table. But removing the column gives a 5474 error (an constraint is dependant on the column 'checked') I've read the http://support.microsoft.com/kb/816755 article about this issue. It says that I need to drop the constraint first. Okay. but then I must manually check what the constraint name is an drop it.. It takes time to check what the name on the constraint is and then type the name.. etc.. So instead I would like to make script which delete this constrain no matter name it would have. Then I can run this script without being sure what the name of this constraint have. I want to make this as fast as possible because some other program may start to fail if this modification takes too long time any suggestions?
From: Jamie Collins on 14 Dec 2007 08:07 On Dec 14, 12:58 pm, "Jeff" <do...(a)spam.me> wrote: > removing the column gives a 5474 error (an > constraint is dependant on the column 'checked') > > I would like to make script which delete this constrain no matter > name it would have. See: http://www.databasejournal.com/img/DropColumn.txt The whole thing may be overkill for you (takes a relatively long time to execute) so you could do as I did and just extract the relevant code. Jamie. --
From: Dan Guzman on 14 Dec 2007 08:25 > So instead I would like to make script which delete this constrain no > matter name it would have. Then I can run this script without being sure > what the name of this constraint have. I suggest you specify a constraint name instead of relying on an auto-generated name: ALTER TABLE dbo.tableA ADD checked bit NOT NULL CONSTRAINT DF_tableA_checked DEFAULT 0; ALTER TABLE dbo.tableA DROP CONSTRAINT DF_tableA_checked; ALTER TABLE dbo.tableA DROP COLUMN checked; -- Hope this helps. Dan Guzman SQL Server MVP "Jeff" <donot(a)spam.me> wrote in message news:%23R8lWElPIHA.4476(a)TK2MSFTNGP06.phx.gbl... > Hey > > Sql Server 2005 > > In a table I added an extra column to a table: > alter table tableA add checked bit default 0 > > Then I give this column a value: > update tableA set checked = 0; > > Now I create a stored procedure which contain 3 select using the column > 'checked' in the where clause. > > after running the stored procedure once, then I want to remove that extra > column I added to the table. But removing the column gives a 5474 error > (an constraint is dependant on the column 'checked') > > I've read the http://support.microsoft.com/kb/816755 article about this > issue. It says that I need to drop the constraint first. Okay. but then I > must manually check what the constraint name is an drop it.. > > It takes time to check what the name on the constraint is and then type > the name.. etc.. > > So instead I would like to make script which delete this constrain no > matter name it would have. Then I can run this script without being sure > what the name of this constraint have. > > I want to make this as fast as possible because some other program may > start to fail if this modification takes too long time > > any suggestions? > > > >
From: Jamie Collins on 14 Dec 2007 10:02 On Dec 14, 1:25 pm, "Dan Guzman" <guzma...(a)nospam- online.sbcglobal.net> wrote: > I suggest you specify a constraint name instead of relying on an > auto-generated name: Omitting a name for a DEFAULT is valid SQL-92 syntax, which would explain why many people (me included) have done so. BTW if SQL Server 2005 doesn't allow ALTER TABLE DROP COLUMN until I've found and dropped the named DEFAULT 'constraint' (I don't think SQL-92 would consider it a CONSTRAINT), does this mean SQL Server 2005 is non-compliant as regards SQL-92? Jamie. --
From: Aaron Bertrand [SQL Server MVP] on 14 Dec 2007 10:33
>> I suggest you specify a constraint name instead of relying on an >> auto-generated name: > > Omitting a name for a DEFAULT is valid SQL-92 syntax, which would > explain why many people (me included) have done so. "Valid" and "best practice" are two different things! :-) A |