Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
itsonlyme4
Posting Yak Master
109 Posts |
 Posted - 2011-01-05 : 10:30:59
|
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 rowsThe ArticleSection table has 15591 rowsI 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 15588Then 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 15590There 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? |
|
|
itsonlyme4
Posting Yak Master
109 Posts |
Posted - 2011-01-06 : 05:25:30
|
| I ended up doing this: Select distinct path from ArticleSectionexcept Select distinct path from ArticleI found 3 rows that were in ArticleSection that weren't in Article. I deleted them. |
 |
|
|
|
|
|
|
|
dropping and readding a FK constraint