Altering constraint in a column in a publication

Hi All,
I have a replicated (published) database with 1 push subscription to it on
another server. Replication type is Transactional with "pushing" occuring
every 2 hours.
1) In BOL it is mentioned, for making a change (besides adding or dropping a
column) in a published table -
a) delete all subscriptions to the publication
b) remove the article/object from the publication
c) change the table as required
d) add the article back to the publication
e) create the subscription again
My questions -
- Do I always need to go through the entire subscription creation process
each time I need to alter a table in my publication? I mean for a table
change that will take 2 minutes, I need to spend 3 hours (thats the time the
snapshot creation takes for me) creating the subscription?
- In step a), does it mean removing the subsribing database ? What about the
subcribing server ?
2) In BOL, its mentioned that to delete subscriptions through Enterprise
Manager, I need to go to SQL Server Group -> <Registration Name> ->
Replication -> Publications -> <Publication name>. In the right pane, I'll
see the subscription. I need to right click on it and say delete.
My questions -
- If I go to Tools -> Replication -> Configure Publishing, Subscribers and
Distributors, go to the Subscribers tab and remove the tick on the
subscriber, is it the same thing as deleting the subcription?
- Secondly, instead of going through the process of creating a new
subscription after altering my table, if I just go back to the above
mentioned location and tick the required subscriber, is it the same thing as
creating a subscription?
- If answers to both above questions is Yes, can I replace steps a) and e)
in question 1) with above two steps (unticking/ticking)?
Unrelated queries -
- If I disable publishing through option "Disable Publishing" in right click
of SQL Server Group -> <Registration Name> -> Replication, is there any
option to "enable" the publishing or do I need to go through the publication
creation process?
- How do I monitor the performance of replication?
Salil.
Salil,
some changes can be made using sp_addscriptexec; it depends on what you are
trying. If this isn't permitted, you can do a no-sync initialization if you
want to avoid the cost of the snapshot. If you go down this path, be sure
that the data is synchronized first.
Dropping the subscription doesn't require you to drop the subscriber's
database, and is distinct from enabling a subscriber.
Disabling publishing has to be followed by enabling publishing if you want
to use replication again.
You can monitor the performance using the replication-specific counters in
Performance Monitor. Also you can query the replication history tables for
short-term monitoring, and msdistribution_status in transactional
replication.
HTH,
Paul Ibison
|||Thank you Paul.
Point 1 (about no-sync initialization) and 4 (replication monitoring)
definitely helps.
But my query about "ticking/unticking" still remains unanswered.
i.e. If I do -
1) Untick subscribing server
2) remove article from publisher
3) alter article
4) add article back to publisher
5) tick subscribing server
Is 1 and 5 the same as "deleting" and "adding" subscirbers respectively?
Salil.
"Paul Ibison" wrote:

> Salil,
> some changes can be made using sp_addscriptexec; it depends on what you are
> trying. If this isn't permitted, you can do a no-sync initialization if you
> want to avoid the cost of the snapshot. If you go down this path, be sure
> that the data is synchronized first.
> Dropping the subscription doesn't require you to drop the subscriber's
> database, and is distinct from enabling a subscriber.
> Disabling publishing has to be followed by enabling publishing if you want
> to use replication again.
> You can monitor the performance using the replication-specific counters in
> Performance Monitor. Also you can query the replication history tables for
> short-term monitoring, and msdistribution_status in transactional
> replication.
> HTH,
> Paul Ibison
>
>
|||Salil,
unticking the subscriber in the distributor properties box will unsubscribe
all subscriptions to this server, not just the one you are concerned about.
Afterwards you'll need to recheck this box and subsequently resubscribe to
the publication.
HTH,
Paul Ibison
|||Thank you Paul.
"Paul Ibison" wrote:

> Salil,
> unticking the subscriber in the distributor properties box will unsubscribe
> all subscriptions to this server, not just the one you are concerned about.
> Afterwards you'll need to recheck this box and subsequently resubscribe to
> the publication.
> HTH,
> Paul Ibison
>
>

Alter table with Merge replication

Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.
Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>

Alter table with Merge replication

Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>

Alter table with Merge replication

Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, after re-creating a table (afm_groups) with a new primary key constraint, I run the following line:
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
go
And get the error:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_flds_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_name'
There is no foreign key constraint built like this with this statement is executed. Any ideas?
This means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>
|||Thanks, Aaron.
You were right. I created an invalid data condition somehow.
Paul Doucette
ARCHIBUS, Inc.

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, aft
er re-creating a table (afm_groups) with a new primary key constraint, I run
the following line:
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
go
And get the error:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_fld
s_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_na
me'
There is no foreign key constraint built like this with this statement is ex
ecuted. Any ideas?This means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>|||Thanks, Aaron.
You were right. I created an invalid data condition somehow.
Paul Doucette
ARCHIBUS, Inc.sql

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, after re-creating a table (afm_groups) with a new primary key constraint, I run the following line
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name
g
And get the error
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_flds_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_name
There is no foreign key constraint built like this with this statement is executed. Any ideasThis means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>

ALTER TABLE statement conflicted with COLUMN FOREIGN KEY

I got the following Error
"ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_ABC_DEF'. The conflict occurred in
database 'Test', table 'DEF', column 'ID'."
when I ran the following scripts:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
GO
ALTER TABLE [dbo].[ABC] ADD
CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
(
[ID]
) REFERENCES [dbo].[DEF] (
[ID]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
My goal was to delete the constraint and recreate it but
the above error indicates that the FK constraint is still
active even when I verified on both tables and there were
not available.
Is this a problem with sqlserver 2000 or the problem is me.
Please help.Post your DDL for table dbo.DEF.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:0ba901c48936$784343d0$3501280a@.phx.gbl...
I got the following Error
"ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_ABC_DEF'. The conflict occurred in
database 'Test', table 'DEF', column 'ID'."
when I ran the following scripts:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
GO
ALTER TABLE [dbo].[ABC] ADD
CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
(
[ID]
) REFERENCES [dbo].[DEF] (
[ID]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
My goal was to delete the constraint and recreate it but
the above error indicates that the FK constraint is still
active even when I verified on both tables and there were
not available.
Is this a problem with sqlserver 2000 or the problem is me.
Please help.|||Perhaps you have existing data that prevents the new constraint from being
created. You can identify this data with the query below:
SELECT *
FROM [dbo].[ABC] AS a
WHERE NOT EXISTS
(
SELECT *
FROM [dbo].[DEF] AS b
WHERE a.[ID] = b.[ID]
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"stoko" <anonymous@.discussions.microsoft.com> wrote in message
news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>|||Actually, we really need the DDL for table dbo.DEF.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
Below is the info you requested. Each time I drop the constraints via sql
analyzer and try recreating them, I have the FK error. I check via EM and
the constraints are not there. What must be going on is beyond my
comprehension. Initially, the first 4 attempts works fine but there-after,
nothing works. Remember that the tables have data.
Let me know...
Thanks in advance.
Stoko.
"Tom Moreau" wrote:
> Post your DDL for table dbo.DEF.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> I got the following Error
> "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_ABC_DEF'. The conflict occurred in
> database 'Test', table 'DEF', column 'ID'."
> when I ran the following scripts:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> GO
>
> ALTER TABLE [dbo].[ABC] ADD
> CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> (
> [ID]
> ) REFERENCES [dbo].[DEF] (
> [ID]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> My goal was to delete the constraint and recreate it but
> the above error indicates that the FK constraint is still
> active even when I verified on both tables and there were
> not available.
> Is this a problem with sqlserver 2000 or the problem is me.
> Please help.
>
>|||On Wed, 8 Sep 2004 12:15:05 -0700, stoko wrote:
>Below is the info you requested. Each time I drop the constraints via sql
>analyzer and try recreating them, I have the FK error. I check via EM and
>the constraints are not there. What must be going on is beyond my
>comprehension. Initially, the first 4 attempts works fine but there-after,
>nothing works. Remember that the tables have data.
>Let me know...
>Thanks in advance.
>Stoko.
(snip code)
Hi Stoko,
The code you supplied works fine for me. And when I append the code from
your original post, I get the following error:
Server: Msg 4902, Level 16, State 1, Line 3
Cannot alter table 'dbo.ABC' because this table does not exist in database
'TestDB80'.
Somehow, you seem to have posted the wrong tables here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||It will save you even more time if you simply provide the DDL for BOTH
tables. I cannot help you until you do that.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:F2F57F9B-D3E5-4B47-A53E-4A2F2750E2FB@.microsoft.com...
Tom,
It would have saved us some time if you renamed the tables to DEF. The DDL
I sent are production tables. I was trying to change the names, etc, but
decided to send you the live information. Thus far, no one has been able to
help explain why I cannot delete and recreate constraints in a sp or script
or dts on tables that have records. The irony is that this thing worked the
first few times and just fails thereafter -- requiring me to recreate the
constraints manually.
I am still waiting for your support.
Thanks.
Stoko.
"Tom Moreau" wrote:
> Actually, we really need the DDL for table dbo.DEF.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:72208196-B2EE-4A61-B46C-0F9C7DA0AAE6@.microsoft.com...
> Below is the info you requested. Each time I drop the constraints via sql
> analyzer and try recreating them, I have the FK error. I check via EM and
> the constraints are not there. What must be going on is beyond my
> comprehension. Initially, the first 4 attempts works fine but
there-after,
> nothing works. Remember that the tables have data.
> Let me know...
> Thanks in advance.
> Stoko.
> "Tom Moreau" wrote:
> > Post your DDL for table dbo.DEF.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "stoko" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0ba901c48936$784343d0$3501280a@.phx.gbl...
> > I got the following Error
> >
> > "ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> > constraint 'FK_ABC_DEF'. The conflict occurred in
> > database 'Test', table 'DEF', column 'ID'."
> >
> > when I ran the following scripts:
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[FK_ABC_DEF]') and OBJECTPROPERTY(id,
> > N'IsForeignKey') = 1)
> > ALTER TABLE [dbo].[ABC] DROP CONSTRAINT FK_ABC_DEF
> > GO
> >
> >
> > ALTER TABLE [dbo].[ABC] ADD
> > CONSTRAINT [FK_ABC_DEF] FOREIGN KEY
> > (
> > [ID]
> > ) REFERENCES [dbo].[DEF] (
> > [ID]
> > ) ON DELETE CASCADE NOT FOR REPLICATION
> > GO
> >
> >
> > My goal was to delete the constraint and recreate it but
> > the above error indicates that the FK constraint is still
> > active even when I verified on both tables and there were
> > not available.
> >
> > Is this a problem with sqlserver 2000 or the problem is me.
> >
> > Please help.
> >
> >
> >
>

alter table nocheck constraint still some dependencies

Hi.

I'm getting errors like this when I try to run an upgrade script I'm trying to
write/test:

altering labels to length 60
Server: Msg 5074, Level 16, State 4, Line 5
The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.

I used this to bracket my script:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
go

/* updates here */

sp_msforeachtable @.command1="print '?'",
@.command2="ALTER TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtable @.command1="print '?'",
@.command2="ALTER TABLE ? ENABLE TRIGGER all"
go

I guess the alter table nocheck constraint isn't disabling the fk's
completely?
Is there a way around this, or do I manually have to do the constraint
dropping/recreating?

Thanks
Jeff KishOn Wed, 17 May 2006 10:25:25 -0400, Jeff Kish wrote:

>Hi.
>I'm getting errors like this when I try to run an upgrade script I'm trying to
>write/test:
>altering labels to length 60
>Server: Msg 5074, Level 16, State 4, Line 5
>The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
>I used this to bracket my script:
>sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
>go
>sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
>go
>/* updates here */
>
>sp_msforeachtable @.command1="print '?'",
>@.command2="ALTER TABLE ? CHECK CONSTRAINT all"
>go
>sp_msforeachtable @.command1="print '?'",
>@.command2="ALTER TABLE ? ENABLE TRIGGER all"
>go
>I guess the alter table nocheck constraint isn't disabling the fk's
>completely?

Hi Jeff,

ALTER TABLE xxx NOCHECK CONSTRAINT yyy is intended to (temporarily)
disable the checking of the constraint. The constraint is not removed
from the metadata. That means that you still can't perform any
modifications that would invalidate the constraint. (Coonsider what
would happpen if you change the datatype of a column on one end of a
FOREIGN KEY constraint but not on the other end and then try to
re-anable the constraint...)

>Is there a way around this, or do I manually have to do the constraint
>dropping/recreating?

If you google for it, you might be able to find scripts to generate the
code to drop and recreate constraints. I've never used any such code, so
I can't comment on the reliability.

--
Hugo Kornelis, SQL Server MVP|||Jeff Kish (jeff.kish@.mro.com) writes:
> I'm getting errors like this when I try to run an upgrade script I'm
> trying to write/test:
> altering labels to length 60
> Server: Msg 5074, Level 16, State 4, Line 5
> The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
> I used this to bracket my script:
> sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
> go

Since you did not include the actual code that implements the change,
I will have to guess. My guess is that you change the length of a
PK column that is referenced by an FK.

If that is the case, you indeed have to drop the FK, as an FK must
always be of the same data type as the key it refers to. SQL Server
cannot know that you are altering both columns, so it only sees that
you are breaking the rule.

> sp_msforeachtable @.command1="print '?'",
> @.command2="ALTER TABLE ? CHECK CONSTRAINT all"

When you reenable constraints, you should use this quirky syntax:

@.command2="ALTER TABLE ? WITH CHEC CHECK CONSTRAINT all"

This forces SQL Server to re-check the constraints. While this take
much longer time, it also means that the optimizer can trust these
constraints and take them in regard when computing a query plan. In
some situations this can have drastic effects on the performance
of the application.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<snip>
>Since you did not include the actual code that implements the change,
>I will have to guess. My guess is that you change the length of a
>PK column that is referenced by an FK.
>If that is the case, you indeed have to drop the FK, as an FK must
>always be of the same data type as the key it refers to. SQL Server
>cannot know that you are altering both columns, so it only sees that
>you are breaking the rule.
>> sp_msforeachtable @.command1="print '?'",
>> @.command2="ALTER TABLE ? CHECK CONSTRAINT all"
>When you reenable constraints, you should use this quirky syntax:
> @.command2="ALTER TABLE ? WITH CHEC CHECK CONSTRAINT all"
>This forces SQL Server to re-check the constraints. While this take
>much longer time, it also means that the optimizer can trust these
>constraints and take them in regard when computing a query plan. In
>some situations this can have drastic effects on the performance
>of the application.
Thanks to both of you, not only for the quick accurate explanation, but also
the reenable recommendation.

I guess I got kind of spoiled by Oracle (I hope that isn't a dirty word here),
but I was able to get things to work better by dropping then re-creating the
constraints.

Yes, I was changing the length of one of the columns in the primary key .

I took some of Erland's other advice I saw elsewhere, and decided not to rely
on any automated tools, and just sat down and grunted through manually
figuring out and implementing the scripts.

regards,

Jeff Kishsql

alter table MyTable with check check constraint all

Hi

Is it correct to use the following statement :

alter table MyTable with check check constraint all

to reenable all the foreign keys and check constraints on a table after a bulk load ?

best regards,

Thibaut Barrère

(follow-up from this on the ssis forum, but it's more a tsql question, hence my post here).Here goes nothing...

-- Disable all constraints for a given table
ALTER TABLE [Some_Table] NOCHECK CONSTRAINT ALL

-- Enable all constraints for a given table
ALTER TABLE [Some_Table] CHECK CONSTRAINT ALL

-- Reseed the identity of a table back to zero (next record will have auto ident set to 1)
DBCC CHECKIDENT ([Some_Table], RESEED, 0)

-- "Run" table constraints on a table to verify that everything is ok
DBCC CHECKCONSTRAINTS ('[Some_Table]')|||

yes.. You can do this..

Advantage for disabling the constraint on bulk load is to increase the performance..

But if any data conflicts with your constraint you can't reenable it again..

|||Hi

the SQL server destination in SSIS disables the constraints itself when the check constraints checkbox is disabled.

Running the alter with check check all seems to work just fine, and properly detects any error.

Thanks for your answers!

Thibautsql

ALTER TABLE (ADD column question)

Hi All,
I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
column with values?
Have any way to do this?
SQL Server 2005
--
ThanksHi
I cannot test it on SQL Server 2005 right now but I did some testing on SQL
Server 2000
CREATE TABLE #Test (col1 INT)
--Insert some data
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
--Alter table
ALTER TABLE #Test ADD col2 INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE #Test ADD CONSTRAINT my_coms UNIQUE NONCLUSTERED (col2)
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>|||"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:<#xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl>...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
You can add a non-nullable column by specifying a default and then dropping
it afterwards. Example:
ALTER TABLE tbl
ADD x INTEGER NOT NULL
CONSTRAINT df_tbl_x DEFAULT (0) ;
ALTER TABLE tbl DROP CONSTRAINT df_tbl_x ;
As for adding the unique constraint, obviously you'll have to populate the
column with unique values first. You haven't told us what this data is or
where it comes from so it's hard to help you with that. Is this supposed to
be a surrogate key? Are you aware of the IDENTITY feature in SQL Server?
David Portas
SQL Server MVP
--|||As David Says, If you want to add a new column which is not null, you MUSt
provide a default non null value - otherwise what will the value for the
existing value for rows be - except null... Afterwords, you can drop the
default if you wish...
This may run long, becuase will have to re-write all of the existing rows.
Also watch your tran log..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ReTF" wrote:

> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
>|||>> I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE , how I can add this column wit
h values? <<
You can use an ALTER TABLE to add the columns. If you also have a
DEFAULT, you will get a single value; if not, you will get a NULL. Use
the NULL, so you can find problems after the UPDATE.
You have a serious problem because someone missed a key in their data
model. You will need to update the new column with the new key, based
on some rule that matches it to the existing key.
Once those values are in place, you then need to check to see that
there are no NULLs and that all values are unique. Then use another
ALTER TABLE to add UNIQUE NOT NULL constraints.
I did this once when merging two inventory systems that used different
part numbers for the same items. It is a pain and you will probalby
have some errors.|||> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
Not unless you use the IDENTITY property or NEWID().
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136385177.110647.17120@.o13g2000cwo.googlegroups.com...
> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
> You have a serious problem because someone missed a key in their data
> model. You will need to update the new column with the new key, based
> on some rule that matches it to the existing key.
> Once those values are in place, you then need to check to see that
> there are no NULLs and that all values are unique. Then use another
> ALTER TABLE to add UNIQUE NOT NULL constraints.
> I did this once when merging two inventory systems that used different
> part numbers for the same items. It is a pain and you will probalby
> have some errors.
>

alter table <table_name> nocheck constraint all QUESTION.

In a stored procedure we use te following construction.
alter table <table_name> nocheck constraint all
<working code>
alter table <table_name> check constraint all
(For more than one table).
(Before and after the transaction the db is consistent,
just not during the transaction).
At the time of switching the constraint on all
constraints should be adhered to, but this is not
checked.
Can the check be done when it is switched back on ?
A check that does not check the complete table, but
just the altered rows ?
thanks for your attention,
ben brugman
EXPLANATION :
Why do we use this switching of of constraints.
In a similar case in Oracle we use defered constraints.
A very oversimplified explenations :
We have a master table and a detail table. The key of
the master table has to be altered so the key FK of the
detail table has to be altered as wel. If the constraint is
left on the first update gives a constraint violation.
The total transaction does updates in over 40 tables with
between 50 and 60 constraints between the those tables.
Ben,
When you re-enable a constraint, you can do that either with check or with nocheck. Looks a bit
funny, though:
alter table <table_name> WITH CHECK check constraint all
I have a feeling that SLQ Server will not only go though the newly added/modified rows, though. A
quick test should tell you that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ben brugman" <ben@.niethier.nl> wrote in message news:e3RkyILlEHA.644@.tk2msftngp13.phx.gbl...
> In a stored procedure we use te following construction.
> alter table <table_name> nocheck constraint all
> <working code>
> alter table <table_name> check constraint all
> (For more than one table).
> (Before and after the transaction the db is consistent,
> just not during the transaction).
> At the time of switching the constraint on all
> constraints should be adhered to, but this is not
> checked.
> Can the check be done when it is switched back on ?
> A check that does not check the complete table, but
> just the altered rows ?
> thanks for your attention,
> ben brugman
> EXPLANATION :
> Why do we use this switching of of constraints.
> In a similar case in Oracle we use defered constraints.
> A very oversimplified explenations :
> We have a master table and a detail table. The key of
> the master table has to be altered so the key FK of the
> detail table has to be altered as wel. If the constraint is
> left on the first update gives a constraint violation.
> The total transaction does updates in over 40 tables with
> between 50 and 60 constraints between the those tables.
>
>
|||Thanks Tibor,
You jolted my memory.
I did consider the with check option before but because
of the longer check time did not use it. As said the consistency
before and after the transaction 'should' be correct.
At the moment the complete Stored procedure takes about
6 seconds (was 2 secs) and does only a few (less than 10) updates in that
time. Most time probably goes into getting the resources, doing
no updates on them (because no rows are selected) and
giving the resources back. (And doing this for over 40 tables).
I am looking into cascaded update for this. Probably far more efficient,
but I do not dare to take the plunge yet.
Should do some test with that though.
Thanks for your quick response,
ben brugman
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uknFQlLlEHA.2380@.TK2MSFTNGP14.phx.gbl...
> Ben,
> When you re-enable a constraint, you can do that either with check or with
nocheck. Looks a bit
> funny, though:
> alter table <table_name> WITH CHECK check constraint all
> I have a feeling that SLQ Server will not only go though the newly
added/modified rows, though. A
> quick test should tell you that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ben brugman" <ben@.niethier.nl> wrote in message
news:e3RkyILlEHA.644@.tk2msftngp13.phx.gbl...
>

alter table <table_name> nocheck constraint all QUESTION.

In a stored procedure we use te following construction.
alter table <table_name> nocheck constraint all
<working code>
alter table <table_name> check constraint all
(For more than one table).
(Before and after the transaction the db is consistent,
just not during the transaction).
At the time of switching the constraint on all
constraints should be adhered to, but this is not
checked.
Can the check be done when it is switched back on ?
A check that does not check the complete table, but
just the altered rows ?
thanks for your attention,
ben brugman
EXPLANATION :
Why do we use this switching of of constraints.
In a similar case in Oracle we use defered constraints.
A very oversimplified explenations :
We have a master table and a detail table. The key of
the master table has to be altered so the key FK of the
detail table has to be altered as wel. If the constraint is
left on the first update gives a constraint violation.
The total transaction does updates in over 40 tables with
between 50 and 60 constraints between the those tables.Ben,
When you re-enable a constraint, you can do that either with check or with nocheck. Looks a bit
funny, though:
alter table <table_name> WITH CHECK check constraint all
I have a feeling that SLQ Server will not only go though the newly added/modified rows, though. A
quick test should tell you that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ben brugman" <ben@.niethier.nl> wrote in message news:e3RkyILlEHA.644@.tk2msftngp13.phx.gbl...
> In a stored procedure we use te following construction.
> alter table <table_name> nocheck constraint all
> <working code>
> alter table <table_name> check constraint all
> (For more than one table).
> (Before and after the transaction the db is consistent,
> just not during the transaction).
> At the time of switching the constraint on all
> constraints should be adhered to, but this is not
> checked.
> Can the check be done when it is switched back on ?
> A check that does not check the complete table, but
> just the altered rows ?
> thanks for your attention,
> ben brugman
> EXPLANATION :
> Why do we use this switching of of constraints.
> In a similar case in Oracle we use defered constraints.
> A very oversimplified explenations :
> We have a master table and a detail table. The key of
> the master table has to be altered so the key FK of the
> detail table has to be altered as wel. If the constraint is
> left on the first update gives a constraint violation.
> The total transaction does updates in over 40 tables with
> between 50 and 60 constraints between the those tables.
>
>|||Thanks Tibor,
You jolted my memory.
I did consider the with check option before but because
of the longer check time did not use it. As said the consistency
before and after the transaction 'should' be correct.
At the moment the complete Stored procedure takes about
6 seconds (was 2 secs) and does only a few (less than 10) updates in that
time. Most time probably goes into getting the resources, doing
no updates on them (because no rows are selected) and
giving the resources back. (And doing this for over 40 tables).
I am looking into cascaded update for this. Probably far more efficient,
but I do not dare to take the plunge yet.
Should do some test with that though.
Thanks for your quick response,
ben brugman
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uknFQlLlEHA.2380@.TK2MSFTNGP14.phx.gbl...
> Ben,
> When you re-enable a constraint, you can do that either with check or with
nocheck. Looks a bit
> funny, though:
> alter table <table_name> WITH CHECK check constraint all
> I have a feeling that SLQ Server will not only go though the newly
added/modified rows, though. A
> quick test should tell you that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ben brugman" <ben@.niethier.nl> wrote in message
news:e3RkyILlEHA.644@.tk2msftngp13.phx.gbl...
> > In a stored procedure we use te following construction.
> >
> > alter table <table_name> nocheck constraint all
> > <working code>
> > alter table <table_name> check constraint all
> >
> > (For more than one table).
> > (Before and after the transaction the db is consistent,
> > just not during the transaction).
> >
> > At the time of switching the constraint on all
> > constraints should be adhered to, but this is not
> > checked.
> >
> > Can the check be done when it is switched back on ?
> > A check that does not check the complete table, but
> > just the altered rows ?
> >
> > thanks for your attention,
> > ben brugman
> >
> > EXPLANATION :
> > Why do we use this switching of of constraints.
> > In a similar case in Oracle we use defered constraints.
> > A very oversimplified explenations :
> > We have a master table and a detail table. The key of
> > the master table has to be altered so the key FK of the
> > detail table has to be altered as wel. If the constraint is
> > left on the first update gives a constraint violation.
> >
> > The total transaction does updates in over 40 tables with
> > between 50 and 60 constraints between the those tables.
> >
> >
> >
> >
>

Alter Table - Add Constraint

Hi All,
Is there a way to SPEED up the process of alter table - Add constraint on a
table with PRE_EXISTING data? This is a LARGE table.
What I am trying to do here is REMOVE the old constraint and add a NEW
one...
Here is what I am figuring as a way to speed things up...
1) bcp out
2) drop constraints
3) truncate table
4) bcp in
5) add constraints
Found this link while searching past posts:
http://sqljunkies.com/WebLog/amacha...onstraints.aspx
Thanks...MALTER TABLE WITH NOCHECK
prevents the new restraint from checking existing data at add time.
"Michelle" <smiley2211@.yahoo.com> wrote in message
news:etvWhKyWFHA.3348@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Is there a way to SPEED up the process of alter table - Add constraint on
a
> table with PRE_EXISTING data? This is a LARGE table.
> What I am trying to do here is REMOVE the old constraint and add a NEW one
..
> Here is what I am figuring as a way to speed things up...
> 1) bcp out
> 2) drop constraints
> 3) truncate table
> 4) bcp in
> 5) add constraints
> Found this link while searching past posts:
> [url]http://sqljunkies.com/WebLog/amachanic/articles/ScriptTableConstraints.aspx[/url
]
>
> Thanks...M
>|||But note that it only applies for check and FK constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:uUJn2lyWFHA.2060@.tk2msftngp13.phx.gbl...
> ALTER TABLE WITH NOCHECK
> prevents the new restraint from checking existing data at add time.
>
> "Michelle" <smiley2211@.yahoo.com> wrote in message news:etvWhKyWFHA.3348@.T
K2MSFTNGP14.phx.gbl...
>

Alter Table

I am new to SQL and struggling with some basic code!
How do I use the ALTER TABLE command to add a foreign key constraint?
I have the correct code for creating the tables and adding the constraints
when creating, but can't figure out how to modify an existing table and add
a FK.
ThanksHi,
Sample code:-
create table t90(i int primary key)
go
create table t91(i int)
go
alter table t91 add constraint fk_t1 foreign key(i) references t90(i)
--
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uQUuKStTEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am new to SQL and struggling with some basic code!
> How do I use the ALTER TABLE command to add a foreign key constraint?
> I have the correct code for creating the tables and adding the constraints
> when creating, but can't figure out how to modify an existing table and
add
> a FK.
> Thanks
>|||This should do:
ALTER TABLE dbo.[Tablex] ADD CONSTRAINT
FK_Tablex_Tabley FOREIGN KEY
(
id
) REFERENCES dbo.Table1
(
id
)
GO
BTW,
if you require syntax like this, you can get EM to create
it for you. Just add the FK in table design and the "Save
Change Script" button becomes available.
HTH,
Paul Ibison

Alter column to set default

I know that the correct syntax to set the default on a column in SQL Server 2005 is:

Alter Table <TableName> Add Constraint <ConstraintName> Default <DefaultValue> For <ColumnName>

But from what I can gather, the SQL-92 syntax is:

Alter Table <TableName> Alter Column <ColumnName> Set Default <DefaultValue>

This generates an error on SQL Server 2005.

Am I wrong about the standard syntax for this statement? If this is the standard, why doesn't SQL Server 2005 support it? I am trying to avoid code that will only work on certain database managers.

Thanks.

SQL Server in only entry level SQL-92 compliant. There are however some features that are full level and so on. The alter table syntax to add a default is not supported yet. If you want to use code that works on various database systems then it is best to stick to CREATE TABLE DDL with basic SQL-92 syntax. This will have a better chance of executing against more database systems. So define the defaults/constraints etc as part of the CREATE TABLE itself. You have to use ALTER TABLE on a case-by-case basis. The syntax differences are huge between ANSI SQL standard, SQL Server, Oracle and DB2 for various DDLs.

Alter Column datatype with Default constraint

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2) Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thxyes thats right,

the constraint has a dependency on the column and hence the data type of the column.

If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

drop the constriant, then do what you need to do to the column

Cheers

Alter column add constraint unique

Is it possible to alter a table column data type AND add a unique
constraint at the same time?
I can get this to work
ALTER TABLE tablename ALTER COLUMN colName DataType(optional size);
and I can get this to work
ALTER TABLE tablename ADD CONSTRAINT UQ_myConstraint UNIQUE
but I can't get both to work at once and don't feel BOL is very
clear.
ThanksNo, you have to do it one at a time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:e64ur1p66uqe09e2r5e6njer7r8mc0bms7@.
4ax.com...
> Is it possible to alter a table column data type AND add a unique
> constraint at the same time?
> I can get this to work
> ALTER TABLE tablename ALTER COLUMN colName DataType(optional size);
> and I can get this to work
> ALTER TABLE tablename ADD CONSTRAINT UQ_myConstraint UNIQUE
> but I can't get both to work at once and don't feel BOL is very
> clear.
> Thanks|||Think about the BASICS!!
SQL is a set oriented language. Everything happens at once. If I
created a column, how the hell would I assign a unique value to each
row' Such things would be ordered an there is no order in RM.|||Then any other constraints will also have to be done seperately, for
instance - DEFAULT.
Correct?
Thanks for the replies
Jeff
On Sat, 07 Jan 2006 00:57:06 GMT, Jeff User <jeff31162@.hotmail.com>
wrote:

>Is it possible to alter a table column data type AND add a unique
>constraint at the same time?
>I can get this to work
>ALTER TABLE tablename ALTER COLUMN colName DataType(optional size);
>and I can get this to work
>ALTER TABLE tablename ADD CONSTRAINT UQ_myConstraint UNIQUE
>but I can't get both to work at once and don't feel BOL is very
>clear.
>Thanks|||No, check constraints and default constraints can be defined with the
column:
ALTER TABLE tbl
ADD SomeCol INT NOT NULL DEFAULT (10)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:6kdur1lfa8s38pjr730eets11vjpsnggkh@.
4ax.com...
> Then any other constraints will also have to be done seperately, for
> instance - DEFAULT.
> Correct?
> Thanks for the replies
> Jeff
> On Sat, 07 Jan 2006 00:57:06 GMT, Jeff User <jeff31162@.hotmail.com>
> wrote:
>
>|||That is adding a new column. And it works well.
But what about altering an existing column?
Assuming there is no existing Default value:
ALTER TABLE tester
ALTER COLUMN fld9 varchar(30) NOT NULL DEFAULT 'hello'
This doesn't work, I get error near DEFAULT.
I think Adding DEFAULT has to be done seperately. This works:
ALTER TABLE tester
ADD CONSTRAINT makeup_a_name DEFAULT 'test value' FOR fieldName
If there is a way though, to combine these, I would be mighty
interested.
Jeff
On Fri, 6 Jan 2006 22:42:56 -0500, "Adam Machanic"
<amachanic@.hotmail._removetoemail_.com> wrote:

>No, check constraints and default constraints can be defined with the
>column:
>ALTER TABLE tbl
>ADD SomeCol INT NOT NULL DEFAULT (10)
>
>--
>Adam Machanic
>Pro SQL Server 2005, available now
>http://www.apress.com/book/bookDisplay.html?bID=457|||No, there isn't a way to combine them. Column constraints can only be
defined when creating columns...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jeff User" <jeff31162@.hotmail.com> wrote in message
news:pmeur1hgvletj6jvu7tsfffol5het9tj7q@.
4ax.com...
> That is adding a new column. And it works well.
> But what about altering an existing column?
> Assuming there is no existing Default value:
> ALTER TABLE tester
> ALTER COLUMN fld9 varchar(30) NOT NULL DEFAULT 'hello'
> This doesn't work, I get error near DEFAULT.
> I think Adding DEFAULT has to be done seperately. This works:
> ALTER TABLE tester
> ADD CONSTRAINT makeup_a_name DEFAULT 'test value' FOR fieldName
> If there is a way though, to combine these, I would be mighty
> interested.
> Jeff
> On Fri, 6 Jan 2006 22:42:56 -0500, "Adam Machanic"
> <amachanic@.hotmail._removetoemail_.com> wrote:
>
>|||IDENTITY property
NEWID()
Have a default based from the result of a UDF value.
Order aside there are times when adding say a column with the IDENTIYY
property is really useful - consider data cleansing, siutation where you are
merging the output from two systems to get rid of duplicates.
Why go to the hassle of adding a new column and then having to write your
own unique number generator, simple type the extra 20 or so characters and
the ALTER TABLE statement will do it for you - KISS (Keep It Simple Sweet)
rather than spinning out the work required so you get paid more.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136602552.703133.30640@.f14g2000cwb.googlegroups.com...
> Think about the BASICS!!
> SQL is a set oriented language. Everything happens at once. If I
> created a column, how the hell would I assign a unique value to each
> row' Such things would be ordered an there is no order in RM.
>|||You would have to drop the existing DEFAULT constraint first. So
BEGIN TRANSACTION
ALTER TABLE .. DROP CONSTRAINT old_default
ALTER TABLE .. ADD COSNTRAINT new_default DEFAULT .. FOR column
COMMIT TRANSACTION
With the proper transaction isolation level, the transaction wrapper
prevent changes between the two statements.
Gert-Jan
Jeff User wrote:
> That is adding a new column. And it works well.
> But what about altering an existing column?
> Assuming there is no existing Default value:
> ALTER TABLE tester
> ALTER COLUMN fld9 varchar(30) NOT NULL DEFAULT 'hello'
> This doesn't work, I get error near DEFAULT.
> I think Adding DEFAULT has to be done seperately. This works:
> ALTER TABLE tester
> ADD CONSTRAINT makeup_a_name DEFAULT 'test value' FOR fieldName
> If there is a way though, to combine these, I would be mighty
> interested.
> Jeff
> On Fri, 6 Jan 2006 22:42:56 -0500, "Adam Machanic"
> <amachanic@.hotmail._removetoemail_.com> wrote:
>

Alter a constraint?

Is there a way to add a column to a PRIMARY KEY constraint (without
deleting and recreating it?) Thanks.No.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1e319f3d61c2ebb1989918@.msnews.microsoft.com...
> Is there a way to add a column to a PRIMARY KEY constraint (without
> deleting and recreating it?) Thanks.|||No, there is no ALTER CONSTRAINT. You will need to DROP/CREATE.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1e319f3d61c2ebb1989918@.msnews.microsoft.com...
> Is there a way to add a column to a PRIMARY KEY constraint (without
> deleting and recreating it?) Thanks.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Is there a way to add a column to a PRIMARY KEY constraint (without
> deleting and recreating it?) Thanks.
No, for a pure index it is possible by adding the WITH DROP_EXISTING
clause.
If there is a reference to the PK from other tables, it's quite a complex
operation anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What is PK?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974AC0BF1D4DAYazorman@.127.0.0.1...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> No, for a pure index it is possible by adding the WITH DROP_EXISTING
> clause.
> If there is a reference to the PK from other tables, it's quite a complex
> operation anyway.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Primary Key

> What is PK?|||duh... I should had knew that.
Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uBvY3THGGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Primary Key
>
>|||Some DBAs prefer this one: http://en.wikipedia.org/wiki/PK_machine_gun
;)
ML
http://milambda.blogspot.com/|||I used that all the time in a game call Battlefield 2 by EA. Its my best
choice of weapon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:7CFE203C-8622-47BE-B144-F1573326C180@.microsoft.com...
> Some DBAs prefer this one: http://en.wikipedia.org/wiki/PK_machine_gun
> ;)
>
> ML
> --
> http://milambda.blogspot.com/|||The real one is slightly more difficult to handle. :) I prefer the AK. I
guess the US government is now alert to this conversation. ;)
ML
http://milambda.blogspot.com/