dropping and readding a FK constraint

[jpotucek]

I have a data conversion to tackle. They are moving from a Windows to a Linux platform on the application side (this is a website deployment app) . The database contains many tables with a PATH column. The path column references the location of a file on the deployment server. Because we are moving from windows to Linux, all of the 'paths' in the database must change. for example, 'templatedata\insurance\news\data\fr\news5192010_fr' will change to 'templatedata/insurance/news/data/fr/news5192010_fr'

Since this 'PATH' column is the Primary Key column in almost every table and there are quite a few FK constraints referencing this 'PATH' I started out by making sure that I would be able to drop and recreate all Primary Key and Foreign Key constraints. I made a copy of the database. Dropped all FK constrainsts, dropped all Primary Keys. I then added back the Primary Keys and was able to add back all FK constraints except one.

Here is the DLL for both tables

 -- Create Table dbo.article
--------------------------------------------------
Create table dbo.article 
(id int identity,
 IW_State VARCHAR(255)not null,
 path VARCHAR(255)not null,
 contentArea CHAR(10)not null,
 homepage CHAR(5) null,
 title NVARCHAR(400)null,
 summary NVARCHAR(1000)null,
 keywords NVARCHAR(50)not null,
 author NVARCHAR(50)null,
 type CHAR(10) not null,
 subArea CHAR(10)null,
 publishDate datetime not null,
 expireDate  datetime  not null,
 articleLanguage CHAR(5) not null,
 indexImage VARCHAR(255) null,
 eventStartDate datetime null,
 eventEndDate datetime null,
 eventLocation NVARCHAR(50) null,
 agentID CHAR(10)null,
 ccText ntext  null,
 indexImageCaption  NVARCHAR(100)  null)  ;

--------------------------------------------------
-- Create Primary Key PK_Article
--------------------------------------------------
alter table dbo.article 
        add constraint PK_Article 
        primary key (path);
--------------------------------------------------


--------------------------------------------------

-- Create Table dbo.articlesection
--------------------------------------------------
Create table dbo.articlesection 
(path VARCHAR(255)not null,
  heading NVARCHAR(255) null,
  body ntext null,
  imagePath VARCHAR(255)null,
  imageCaption NVARCHAR(500)null,
  sortOrder int not null,
  isHighlight CHAR(5)null);

--------------------------------------------------
-- Create Primary Key PK_ArticleSection
--------------------------------------------------
alter table dbo.articlesection 
        add constraint PK_ArticleSection 
        primary key (path, sortOrder);

--------------------------------------------------
-- Create Foreign Key FK_ArticleSection_Article
--------------------------------------------------
alter table dbo.articlesection 
        add constraint FK_ArticleSection_Article 
        foreign key (path) 
        references dbo.article (path) 
        On Delete No Action
        On Update No Action;]

When I attempt to recreate the constraint FK_ArticleSection_Article on the articlesection table, I get this error:

23000(547)[Microsoft][ODBC SQL Server Driver][SQL Server]The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ArticleSection_Article". The conflict occurred in database "CMTest", table "dbo.article", column 'path'. (0.08 secs)

The article table has 4389 rows
The ArticleSection table has 15591 rows


I did find something weird.

I did a select on the article table using "where path = " and included ALL paths in the article table and I get a row count of 4389 (which I expected)

when I do a select on the article table using "where path <> " and included ALL paths in the article table, I get a row count of 4388 ????

I then did a select on the articlesection table using "where path = " and included ALL paths in the article table, I got a row count of 15588

Then I did a select on the articlesection table using "where path <> " and included ALL paths in the article table, I got a row count of 15590

There is a mismatch somewhere (a value exists in the path column in article that exists in articlesection or vice-versa) but I really can't figure out how to find it!!!

Can anyone help?