January 5, 2011 at 7:38 am
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?
January 5, 2011 at 7:35 pm
Try this:
SELECT * FROM articlesection s LEFT JOIN article a
ON s.path = a.path
WHERE a.path IS NULL
and then other way around:
SELECT * FROM article s LEFT JOIN articlesection a
ON s.path = a.path
WHERE a.path IS NULL
January 6, 2011 at 3:24 am
Thank you so much for the reply. what I ended up doing was
Select distinct path from ArticleSection
except
Select distinct path from Article
I found 3 rows that were in ArticleSection that weren't in Article. I deleted them.
Thanks again for the help!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply