Data SWITCH partition fails with primary key constraint Error

Hi champs,

I am trying to use SWITCH partitions from one fact table out to another identical table. On some tables this does not work as I get an ERROR conserning primary key constraints; is there a way around this without deleting the primary key constraint?

ERROR:

"SWITCH PARTITION 1 TO my_switch_out_table PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'my_fact_table' contains primary key for constraint "

/Many thanks

Please post some sample DDL that demonstrates the problem. It will be easier to suggest the solution. You should also take a look at the BOL topic below:

http://msdn2.microsoft.com/en-gb/library/ms191160.aspx

It lists the table, index and constraint requirements for the switch to work.

|||

I have one table that has a two colums as a PK and this table has a PK constraint to one other table and other constraints to 5 other tables.

I've constructed the "OLD_DATA" table as a exact duplicate, including index, of the source table.

However I cannot create the exact same constraints on the destination table, as these already exists in the database.

when I run the following SWITCH, I get an error that

ALTER TABLE dbo.source_table_fact
SWITCH PARTITION 1
TO dbo.OLD_DATA_source_table_fact
PARTITION 1
go

[Execute SQL Task] Error: Executing the query "ALTER TABLE dbo.Ordination_fact SWITCH PARTITION 1 TO dbo.OLD_DATA_MYtable_fact PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'dbo.MYtable_fact' contains primary key for constraint 'FK_fact_Mytable_fact'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This works fine on most of my fact tables but some SWITCHES will not work.

/Many thanks

|||

Here is the script
/* script start */
USE master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'testdb')
BEGIN
ALTER DATABASE [testdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [testdb]
END
go
CREATE DATABASE [testdb]
GO
USE [testdb]
GO

CREATE PARTITION FUNCTION [RangeMonth] (datetime)
AS RANGE RIGHT FOR VALUES (
N'2006-12-01 00:00:00',
N'2007-01-01 00:00:00',
N'2007-02-01 00:00:00',
N'2007-03-01 00:00:00',
N'2007-04-01 00:00:00',
N'2007-05-01 00:00:00'
);
GO

CREATE PARTITION SCHEME [RangeM]
AS PARTITION [RangeMonth] all
TO ([Primary]);

CREATE TABLE dbo.test1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT test1_pk
PRIMARY KEY NONCLUSTERED (A ,ST) ON [RangeM] (ST),
) on [RangeM] (ST)
;

CREATE TABLE dbo.Lefttest1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT Lefttest1_pk
PRIMARY KEY NONCLUSTERED (A,ST) ON [Primary]
) ON [Primary]
;

CREATE TABLE dbo.test2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT test2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [RangeM] ([PartKey]),
) ON [RangeM] ([PartKey])
go

CREATE TABLE dbo.Lefttest2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT Lefttest2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [Primary],
) ON [Primary]

;

CREATE TABLE dbo.test3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint test3_pk
primary key nonclustered (G),

constraint test3_D_PartKey_ref
foreign key (D,PartKey)
references test2(D,PartKey)
on delete cascade
)
;
GO
CREATE TABLE dbo.Lefttest3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint Lefttest3_pk
primary key nonclustered (G) ON [Primary],

constraint Lefttest3_D_PartKey_ref
foreign key (D,PartKey)
references Lefttest2(D,PartKey)
on delete cascade
) ON [Primary]
;
GO

-- Try to switch out the first partition
-- on test1

ALTER TABLE test1
SWITCH PARTITION 1
TO Lefttest1;

-- Try to switch out the first partition
-- on test2
-- Fails with
-- Msg 4967, Level 16, State 1, Line 1
-- ALTER TABLE SWITCH statement failed.
-- SWITCH is not allowed because source table 'testdb.dbo.test2'
-- contains primary key for constraint 'test3_did_ref'.

ALTER TABLE test2
SWITCH PARTITION 1
TO Lefttest2;

data lost

Hi,

I have encountered problem in the merge replication with conflict caused by foreign key constraint. As I did some reseach that it can be avoided by increase the upload and download batch generations.

My question is how can I recover the data which has been lost?

Thanks

You can try the mscontents and the conflict tables to say if you can recover the data there if these table has not been cleaned up yet.

I am more interested in how the data was lost. is there any nonconvergence between publisher and subscriber data ?

are you using Sql2000 or SQL2005 ?

thanks

Yunwen

|||When you get a conflict, the info is stored in conflict tables, which you can see in the Replication Conflict Viewer. Conflicts are kept here until you pick which one wins.|||

Thank you for your response.

The conflict message says:"INSERT statement conflicted with COLUMN FOREIGN KEY constraint" so the record can not be inserted in the child table on the subscriber site.

Then somehow the record is also deleted in the child table on the publisher site.

I may be able to get the rowguid from the msmerge_delete_conflicts table, but no more the detail data like stored in the [conflict_replication name_table name]

It seems the data get lost permanently

|||I am using SQL2000|||

If you are using SQL Server 2000 SP4, then please look at the option of 'compensate_for_errors'. This is a paramtere to sp_addmergearticle/sp_changemergearticle.

More info in this article: http://support.microsoft.com/kb/828637

Data handling in sysindexes table

Hi,
I require your help in understanding the way data is maintained in
sysindexes table, I have two doubts,
1) While creating a foreign key constraint for a table, new entry is made in
this table, after dropping this constraint, the entry added with indid equal
to 2 is not deleted from sysindexes table.
2) When we transfer the data from one file group to another, groupid field
value for
for indid equal to 2 still shows the old file group value.
Please execute the following sql statements to simulate the scenario I am
talking about.
create table Department
(DeptId int not null,
DeptName varchar(100))
go
insert into Department
values(1, 'One')
insert into Department
values(2, 'two')
insert into Department
values(3, 'three')
select * from sysindexes
where id = object_id('Department')
-- you will see one entry with indid equal to 0
alter table Department
add constraint PK_DeptId PRIMARY KEY (DeptId)
go
select * from sysindexes
where id = object_id('Department')
-- you will see one entry with indid equal to 1
create table Employee
(EmpId int not null,
EmpName varchar(100),
DeptId int not null)
go
select * from sysindexes
where id = object_id('Employee')
insert into employee
values(1, 'Emp1', 1)
insert into employee
values(2, 'Emp2', 2)
insert into employee
values(3, 'Emp3', 3)
-- you will see one entry with indid equal to 0
alter table Employee
add constraint PK_EmpId PRIMARY KEY (EmpId)
go
alter table Employee
add constraint FK_Emp_DeptId FOREIGN KEY (DeptId)
REFERENCES Department(DeptId)
go
select * from sysindexes
where id = object_id('Employee')
-- you will see two entries with indid values 1 and 2
alter table Employee
drop constraint FK_Emp_DeptId
go
DBCC dbreindex ('Employee', '', 50)
DBCC dbreindex ('Department', '', 50)
select * from sysindexes
where id = object_id('Employee')
-- still you will see those two entries, and indid 2 is not removed from
sysindexes table.
To change the file group, I follow the steps given below, after completing
these steps
groupid value with '2' in sysindexs table for employee object is not getting
updated to
the new file group id value.
1) We drop the employee table's clustered index and recreate clustered index
on the new file group, after this group id field in sysindexes table is
changed
to the new file group in employee table for the row where indid is 1.
2) We drop the foreign key constraint in employee table.
3) We drop the department table's clustered index and recreate clustered
index
on the new file group, after this group id field in sysindexes table is
changed
to the new file group in department table.
4) Foreign key constraint is created again on the employee table by
referring department
table.
5) Execute this statement, select * from sysindexes where id =
object_id('Employee')
6) Groupid field in sysindexes table for employee table against indid value
2 still
shows the old file group id.
This behaviour in sysindexes table is bit confusing, please help me out,
thank you.
Regards,
DevaI think the changes will get reflected when the Server feels that the
statisctics are out of date.
Try
EXEC sp_updatestats
before you check for the change. Its just a guess.
Let me know if this worked.|||Hi,
Data in sysindexes table is not updated even after running
EXEC sp_updatestats
statement, thank you.
Regards,
Deva|||Can you try
DBCC UPDATEUSAGE ('<DBNAME>','<TABLENAME>')
--
"Deva" wrote:

> Hi,
> Data in sysindexes table is not updated even after running
> EXEC sp_updatestats
> statement, thank you.
> Regards,
> Deva
>|||Hi,
Bad luck, even updateusage command does not refresh the data in sysindexes
table, thank you.
Regards,
Deva
"Omnibuzz" wrote:
> Can you try
> DBCC UPDATEUSAGE ('<DBNAME>','<TABLENAME>')
> --
>
>
> "Deva" wrote:
>|||Deva
sp_helpindex 'Employee'
sp_helpconstraint 'Employee'
Yep, it seems to be fixed in SQL Server 2005
select * from sys.indexes
where object_id= object_id('Employee')
Shows only one row
"Deva" <Deva@.discussions.microsoft.com> wrote in message
news:E25567E2-F79B-4516-AD31-6CDC58567F32@.microsoft.com...
> Hi,
> Bad luck, even updateusage command does not refresh the data in sysindexes
> table, thank you.
> Regards,
> Deva
>
> "Omnibuzz" wrote:
>|||1) This is most likely statistics created. What is the name of that "index"?
2) Since statistics doesn't store any pages, the file group is irrelevant. M
ost probably Ms decided
not to change this (dummy) value in case you move the physical table, since
the value should really
be NULL in the first place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deva" <Deva@.discussions.microsoft.com> wrote in message
news:E422EDEF-5DA1-4135-97EF-DCA8637E7B63@.microsoft.com...
> Hi,
> I require your help in understanding the way data is maintained in
> sysindexes table, I have two doubts,
> 1) While creating a foreign key constraint for a table, new entry is made
in
> this table, after dropping this constraint, the entry added with indid equ
al
> to 2 is not deleted from sysindexes table.
> 2) When we transfer the data from one file group to another, groupid field
> value for
> for indid equal to 2 still shows the old file group value.
> Please execute the following sql statements to simulate the scenario I am
> talking about.
> create table Department
> (DeptId int not null,
> DeptName varchar(100))
> go
> insert into Department
> values(1, 'One')
> insert into Department
> values(2, 'two')
> insert into Department
> values(3, 'three')
> select * from sysindexes
> where id = object_id('Department')
> -- you will see one entry with indid equal to 0
> alter table Department
> add constraint PK_DeptId PRIMARY KEY (DeptId)
> go
> select * from sysindexes
> where id = object_id('Department')
> -- you will see one entry with indid equal to 1
> create table Employee
> (EmpId int not null,
> EmpName varchar(100),
> DeptId int not null)
> go
> select * from sysindexes
> where id = object_id('Employee')
> insert into employee
> values(1, 'Emp1', 1)
> insert into employee
> values(2, 'Emp2', 2)
> insert into employee
> values(3, 'Emp3', 3)
> -- you will see one entry with indid equal to 0
> alter table Employee
> add constraint PK_EmpId PRIMARY KEY (EmpId)
> go
> alter table Employee
> add constraint FK_Emp_DeptId FOREIGN KEY (DeptId)
> REFERENCES Department(DeptId)
> go
> select * from sysindexes
> where id = object_id('Employee')
> -- you will see two entries with indid values 1 and 2
> alter table Employee
> drop constraint FK_Emp_DeptId
> go
> DBCC dbreindex ('Employee', '', 50)
> DBCC dbreindex ('Department', '', 50)
> select * from sysindexes
> where id = object_id('Employee')
> -- still you will see those two entries, and indid 2 is not removed from
> sysindexes table.
> To change the file group, I follow the steps given below, after completing
> these steps
> groupid value with '2' in sysindexs table for employee object is not getti
ng
> updated to
> the new file group id value.
> 1) We drop the employee table's clustered index and recreate clustered ind
ex
> on the new file group, after this group id field in sysindexes table is
> changed
> to the new file group in employee table for the row where indid is 1.
> 2) We drop the foreign key constraint in employee table.
> 3) We drop the department table's clustered index and recreate clustered
> index
> on the new file group, after this group id field in sysindexes table is
> changed
> to the new file group in department table.
> 4) Foreign key constraint is created again on the employee table by
> referring department
> table.
> 5) Execute this statement, select * from sysindexes where id =
> object_id('Employee')
> 6) Groupid field in sysindexes table for employee table against indid valu
e
> 2 still
> shows the old file group id.
> This behaviour in sysindexes table is bit confusing, please help me out,
> thank you.
> Regards,
> Deva|||On Tue, 9 May 2006 22:26:01 -0700, Deva wrote:

>Hi,
>I require your help in understanding the way data is maintained in
>sysindexes table, I have two doubts,
>1) While creating a foreign key constraint for a table, new entry is made i
n
>this table, after dropping this constraint, the entry added with indid equa
l
>to 2 is not deleted from sysindexes table.
>2) When we transfer the data from one file group to another, groupid field
>value for
>for indid equal to 2 still shows the old file group value.
(snip)
Hi Deva,
The extra row in sysindexes that you see is not for an index, but for
column statistics. If you look at the name column in the output from
SELECT * FROM sysindexes WHERE ...
you'll see that the name start with "_WA_Sys_". For experienced SQL
Server user, this is a clear indication that this is not a real index,
but an entry for column statistics.
I don't think it's documented anywhere, but a more reliable way to
seperate statistics entries from real indexes in sysindexes is to use
the status column:
SELECT *
FROM sysindexes
WHERE id = OBJECT_ID('Employee')
AND status & 64 = 0
Or, even better, don't query the system tables at all. Use the supported
stored procedures instead:
EXEC sp_helpindex 'Employee'
Hugo Kornelis, SQL Server MVP|||Hugo,

> AND status & 64 = 0
How about using INDEXPROPERTY() instead? ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:9ga362h8at3nk05gmhtj2hnb5hqfq0sop4@.
4ax.com...
> On Tue, 9 May 2006 22:26:01 -0700, Deva wrote:
>
> (snip)
> Hi Deva,
> The extra row in sysindexes that you see is not for an index, but for
> column statistics. If you look at the name column in the output from
> SELECT * FROM sysindexes WHERE ...
> you'll see that the name start with "_WA_Sys_". For experienced SQL
> Server user, this is a clear indication that this is not a real index,
> but an entry for column statistics.
> I don't think it's documented anywhere, but a more reliable way to
> seperate statistics entries from real indexes in sysindexes is to use
> the status column:
> SELECT *
> FROM sysindexes
> WHERE id = OBJECT_ID('Employee')
> AND status & 64 = 0
> Or, even better, don't query the system tables at all. Use the supported
> stored procedures instead:
> EXEC sp_helpindex 'Employee'
> --
> Hugo Kornelis, SQL Server MVP|||On Wed, 10 May 2006 18:58:09 +0200, Tibor Karaszi wrote:

>Hugo,
>
>How about using INDEXPROPERTY() instead? ;-)
Hi Tibor,
Of course! Much better. (sound of hand slapping forehead)
That's what I get for giving up on BOL in frustration and simply
checking sp_helptext sp_helpindex...
Hugo Kornelis, SQL Server MVP