Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
Ed
You can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed

Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would b
e
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed

Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Edsql

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:

> Is there a way to alter a unique clustered index in a table to a primary k
ey
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just t
o
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...[vbcol=seagreen]
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-Joel
I'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon
|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-JoelI'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-JoelI'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>

alter table.. foreign key

I want to add a foreign key codnegozio references to negozio.idnegozio on table cliente
The table cliente is:
CREATE TABLE Cliente
(User_id VARCHAR(10)PRIMARY KEY ,
Nome VARCHAR2(15) NOT NULL ,
Cognome VARCHAR2(15) NOT NULL,
Indirizzo VARCHAR2(50) NOT NULL ,
CAP NUMBER(6) NOT NULL,
Citt VARCHAR2(50) NOT NULL ,
Provincia VARCHAR2(50) NOT NULL,
Password VARCHAR2(8) NOT NULL ,
Email VARCHAR2(100) NOT NULL,
Credito NUMBER(6) NOT NULL,
Carta_credito NUMBER(10))

and the table negozio is:
CREATE TABLE Negozio
(IdNegozio NUMBER(8) PRIMARY KEY auto-increment,
Ragione_Sociale VARCHAR2(20) NOT NULL,
Indirizzo VARCHAR2(15) NOT NULL ,
CAP NUMBER(6) NOT NULL,
Citt VARCHAR2(15) NOT NULL ,
Provincia VARCHAR2(15) NOT NULL,
Email VARCHAR2(15) NOT NULL)

what' s can I do?
Thank you ElisaIs this Oracle? If so:

alter table Cliente add (codnegozio NUMBER(8));

alter table Cliente add constraint my_constraint_name foreign key (codnegozio) references Negozio;

Alter table with PRIMARY KEY

I have an existing table (with records) having the ff: structure:

CREATE TABLE [dbo].[TEMP2_WORKORDER] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[JobType] [varchar] (3) NULL ,
[JobID] [varchar] (10) NULL ,

I want to be modify the structure to add a PRIMARY KEY to the [WorkOrderID] column.

I was using ALTER TABLE but can't get the right syntax. Please help!

ThanksALTER TABLE dbo.TEMP2_WORKORDER ADD CONSTRAINT
PK_testtable PRIMARY KEY CLUSTERED
(
WorkOrderID
)|||Thank you. It did the trick!

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.
>|||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.
>
>|||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.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id
,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemo
tb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"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.
>
>|||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.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id
,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemo
tb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"Dan Guzman" wrote:

> 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...
>
>|||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)|||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:
>
>|||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:
>
>sql

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 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.
>
|||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.
>
>
|||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.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemotb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"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.
>
>
|||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.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_AliasIDtb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AliasIDtb] DROP CONSTRAINT FK_AliasIDtb_RecipDemotb
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_CMStb_RecipDemotb]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CMStb] DROP CONSTRAINT FK_CMStb_RecipDemotb
GO
CREATE TABLE [dbo].[RecipDemotb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecipSSN] [numeric](18, 0) NULL ,
[RecipLastNM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipFirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipMiddleNM] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipPhone] [numeric](10, 0) NULL ,
[RecipDOB] [datetime] NULL ,
[RecipDOD] [datetime] NULL ,
[RecipAddress] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipAddress2] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipCounty] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipZip] [numeric](11, 0) NULL ,
[RecipRace] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MedIDNM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EPSDTIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipSex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPLIND] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipNMCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecipDOE] [datetime] NULL ,
[RecipIDNUM] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_In_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dup_Card_Code] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AliasIDtb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MAID] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IdBeginDate] [datetime] NULL ,
[IdEndDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CMStb] (
[OriginalRecipid] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CMS_PART_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMS_Beg_Date] [datetime] NULL ,
[CMS_End_Date] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecipDemotb] WITH NOCHECK ADD
CONSTRAINT [PK_RecipDemotb] PRIMARY KEY CLUSTERED
(
[OriginalRecipid]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AliasIDtb] ADD
CONSTRAINT [FK_AliasIDtb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
"Dan Guzman" wrote:

> 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...
>
>
|||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)
|||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:
>
>
|||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:
>
>

alter table and logging

I just did an alter table alter column on a test 7.0 database to change a
non-indexed, non-primary key column of char(2) to a varchar(100). The table
was about a 1G and it logged the whole thing. I guess that's not too
surprising.
Is the only way around this to create a brand new column and then work with
the new column instead?
You are correct. The transaction log must remember the exact actions
taken by all users/roles in a database in order to perform a proper
recovery. You could do a SELECT INTO and make sure SELECT INTO/BULK
COPY is enabled at the database level and that will not be logged.
Shahryar
CLM wrote:

>I just did an alter table alter column on a test 7.0 database to change a
>non-indexed, non-primary key column of char(2) to a varchar(100). The table
>was about a 1G and it logged the whole thing. I guess that's not too
>surprising.
>Is the only way around this to create a brand new column and then work with
>the new column instead?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

alter table and logging

I just did an alter table alter column on a test 7.0 database to change a
non-indexed, non-primary key column of char(2) to a varchar(100). The table
was about a 1G and it logged the whole thing. I guess that's not too
surprising.
Is the only way around this to create a brand new column and then work with
the new column instead?You are correct. The transaction log must remember the exact actions
taken by all users/roles in a database in order to perform a proper
recovery. You could do a SELECT INTO and make sure SELECT INTO/BULK
COPY is enabled at the database level and that will not be logged.
Shahryar
CLM wrote:
>I just did an alter table alter column on a test 7.0 database to change a
>non-indexed, non-primary key column of char(2) to a varchar(100). The table
>was about a 1G and it logged the whole thing. I guess that's not too
>surprising.
>Is the only way around this to create a brand new column and then work with
>the new column instead?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

alter table and logging

I just did an alter table alter column on a test 7.0 database to change a
non-indexed, non-primary key column of char(2) to a varchar(100). The table
was about a 1G and it logged the whole thing. I guess that's not too
surprising.
Is the only way around this to create a brand new column and then work with
the new column instead?You are correct. The transaction log must remember the exact actions
taken by all users/roles in a database in order to perform a proper
recovery. You could do a SELECT INTO and make sure SELECT INTO/BULK
COPY is enabled at the database level and that will not be logged.
Shahryar
CLM wrote:

>I just did an alter table alter column on a test 7.0 database to change a
>non-indexed, non-primary key column of char(2) to a varchar(100). The tabl
e
>was about a 1G and it logged the whole thing. I guess that's not too
>surprising.
>Is the only way around this to create a brand new column and then work with
>the new column instead?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.

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.
Thanks
Hi,
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
>