473,898 Members | 3,235 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Deleting a column DEFAULT....


Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettagl io]
DROP CONSTRAINT [DF__PlanningD__ OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ALTER COLUMN [OreStraordinari o] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ADD DEFAULT ('00.00') FOR [OreStraordinari o]
GO
it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettagl io ALTER COLUMN OreStraordinari o DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.
Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)

Jul 24 '06 #1
6 27287
Giacomo (no*****@grazie .it) writes:
P.S.
We're using SQL Sever 2000 (version 8.00.194)
Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.

No, it will not address your problem at hand, and being at work now I don't
have the time to post the exact code. You will need to read the system
tables to get the name of the default and then use dynamic SQL to run
ALTER TABLE DROP CONSTRAINT. Best practice is to always name your defaults,
so that you don't run into this problem.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 24 '06 #2
Giacomo,

You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.

Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

EXEC sp_rename 'PlanningDettag lio.OreStraordi nario', '__old', 'COLUMN'

ALTER TABLE PlanningDettagl io
ADD OreStraordinari o DECIMAL(18,2) NULL -- or whatever the original
definition

UPDATE PlanningDettagl io
SET OreStraordinari o = __old

ALTER TABLE PlanningDettagl io DROP COLUMN __old

COMMIT TRANSACTION

HTH,
Gert-Jan
Giacomo wrote:
>
Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettagl io]
DROP CONSTRAINT [DF__PlanningD__ OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ALTER COLUMN [OreStraordinari o] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ADD DEFAULT ('00.00') FOR [OreStraordinari o]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettagl io ALTER COLUMN OreStraordinari o DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)
Jul 24 '06 #3
Erland Sommarskog (es****@sommars kog.se) writes:
Giacomo (no*****@grazie .it) writes:
>P.S.
We're using SQL Sever 2000 (version 8.00.194)

Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.

No, it will not address your problem at hand, and being at work now I
don't have the time to post the exact code. You will need to read the
system tables to get the name of the default and then use dynamic SQL to
run ALTER TABLE DROP CONSTRAINT. Best practice is to always name your
defaults, so that you don't run into this problem.
So this is how you do it:

DECLARE @default sysname
SELECT @default = object_name(cde fault)
FROM syscolumns
WHERE id = object_id('your table')
AND name = 'yourcolumn'
EXEC ('ALTER TABLE yourtable DROP CONSTRAINT ' + @default)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 24 '06 #4
Thank you very much,
it works perfectly! :)

Giacomo

Jul 25 '06 #5
Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
<constraint name >The conflict occured in database <databasename>, table
tablename,colum n <columnname>

Kindly give solution of this problem.
sonika.
Gert-Jan Strik wrote:
Giacomo,

You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.

Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

EXEC sp_rename 'PlanningDettag lio.OreStraordi nario', '__old', 'COLUMN'

ALTER TABLE PlanningDettagl io
ADD OreStraordinari o DECIMAL(18,2) NULL -- or whatever the original
definition

UPDATE PlanningDettagl io
SET OreStraordinari o = __old

ALTER TABLE PlanningDettagl io DROP COLUMN __old

COMMIT TRANSACTION

HTH,
Gert-Jan
Giacomo wrote:

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettagl io]
DROP CONSTRAINT [DF__PlanningD__ OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ALTER COLUMN [OreStraordinari o] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettagl io]
ADD DEFAULT ('00.00') FOR [OreStraordinari o]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettagl io ALTER COLUMN OreStraordinari o DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)
Jul 26 '06 #6
sonu (so********@gma il.com) writes:
Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
<constraint name >The conflict occured in database <databasename>, table
tablename,colum n <columnname>

Kindly give solution of this problem.
I'm sorry, but you will have to try again. To start with, your problem
appears to be completely different from what Giacomo had, so please start
a new thread.

As for your problem, you run a DELETE statement and a constraint fires.
I assume that this is a foreign key constraint. Usually foreign keys
are a good thing, as it prevents from you from doing bad things like
deleting a product which are listed on open order items. Then you talk
about diagrams, but it's impossible to understand what you are talking
about. Are you talking about database diagrams in an SQL tool, or is one
of your tables called Diagrams? Please post again, and make an effor to
explain your problem clearly. It helps if you includ the definition of
the involved tables, and the SQL you are having problem with.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 26 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1087
by: was | last post by:
Hi All, I 'm using Oracle 8i and i have a table table1 with two columns: COL1 not null COL2 DEFAULT 'N' if i make an insert : insert into table1(COL1,COL2) values (1,null)
0
1752
by: Tim Johnson | last post by:
Hello: I'm using version 3.23.41. How may I change the default value of an existing table? (type will remain the same) Pointers to docs are welcome and invited. thanks tim
3
13578
by: M Simpson | last post by:
/* for the google index */ ALTER TABLE DEFAULT COLUMN DEFAULT VALUE I've worked out several stored procedures for altering the default column values in a table. They were compiled from books and code snippets found here. It was a pain to work out so I've decided to share my work and research here. This post is just my way of saying thanks to several others here for posting with their wisdom and intelligence.
3
2157
by: Dev | last post by:
Hello all, I am working on setting up a table that will append a sequence to the end of the value inserted. Example; INSERT INTO test (test) VALUES ('abcd'); And have the data in the database be; abcd0001 Now I do have things setup else where were the default value for the field
8
1710
by: nick | last post by:
Hi What is the proper way of setting a boolean column (checkbox) to "true" by default? It seems that the datagrid won't accept inserts if the default is true. BoolColPaid = new DataGridBoolColumn(); BoolColPaid.MappingName = "paid"; BoolColPaid.HeaderText = "paid"; BoolColPaid.FalseValue = false;
4
41870
by: Bob Day | last post by:
Using vs.2003, SQL native 1) I presume that the SQL bit data type is the equivalent of Boolean data type in VS 2003. I have used it that way, and it works fine. 2) Given above, using Server Explorer, Design Table, what can I put in the default field for FALSE for a bit data type column? The IDE accepts ('0') and also (0) as OK (and fails on things like ('False') as I would expect).. But when you create a new row and save it to the...
2
1409
by: mjwills | last post by:
All, I am using VB.NET 1.0 / 2002. I am having trouble setting a column default to "" (note that "" is not the same as Null / DBNull / Nothing) in a typed dataset (to reflect the same database default)... Below is a snippet... <xs:element name="Customers"> <xs:complexType>
4
1536
by: Water Cooler v2 | last post by:
Is there some way I can turn the Default XML Comments off? Each time I create a new class in the project, I get those annoying default XML comments tagged to the class and its default ctor. Damn irritating. I can add XML comments later when I want. I am tired of deleting the default ones that are created by Visual Studio .NET 2003 everytime. I've searched Options but to no avail.
0
955
by: kasukur | last post by:
Hi what i was trying to do is, i am using Dataset to populate data and update the database. The problem is i have a column which is of type Varchar(1), doesn't accept Nulls and default value is 0 when i insert a row through SQL and if i don't enter any value, it inserts 0 but when i try to do the same using Dataset it doesn't seem to be picking the default value and displaying an error message "A value must be entered in the column" ...
0
11259
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10857
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10483
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9661
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8035
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5880
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4706
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4295
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3305
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.