Problem with Fk when syncronizing two databases

Question

Problem with Fk when syncronizing two databases

newbie_dev

SSCommitted

Points: 1916
More actions
May 7, 2009 at 2:58 am

#221008

Hi
I'm comparing two databases with Apex Sql Diff, trying to make the structure the same but I encountered a curious(for me) problem
For some foreign keys in the first database the statement looks like this :
ALTER TABLE [dbo].[Sst_Role_User]
ADD CONSTRAINT [fk_sst_role_user_sst_organizationunit_user]
FOREIGN KEY ([Id_Organizationunit_User]) REFERENCES [dbo].[Sst_Organizationunit_User] ([Id_Organizationunit_User])
ALTER TABLE [dbo].[Sst_Role_User]
CHECK CONSTRAINT [fk_sst_role_user_sst_organizationunit_user]
and in the second like this :
ALTER TABLE [dbo].[Sst_Role_User]
ADD CONSTRAINT [fk_sst_role_user_sst_organizationunit_user]
FOREIGN KEY ([Id_Organizationunit_User]) REFERENCES [dbo].[Sst_Organizationunit_User] ([Id_Organizationunit_User])
I tried to make the second table as the first by running :
ALTER TABLE [dbo].[Sst_Role_User]
CHECK CONSTRAINT [fk_sst_role_user_sst_organizationunit_user]
The statement completed successfully,and if I script the table from SSMS the statement appears.
But when I run the compare again the result is the same.
Is the Apex wrong? or I'm missing something.
10q

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray Laubert SSCrazy Points: 2910 More actions · Answer 1

If I remember correctly, this is a simple matter of which was created first.

In the first set, the FK was added after the database was created, hence the need to add the second part to actually perform the check constraint against the existing data. In the second database, it was probably created from scripting the first database and there fore no need to check data as it does not exist yet.

All you did when you manually tried to correct this is tell SQL to check the data using the constraint.

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

newbie_dev SSCommitted Points: 1916 More actions · Answer 2

aha...ok...

So, it's there a way to make them look alike?because I want them to be the same structurally, when I compare them with Apex Sql to see no difference.Is this possible, or they will remain the same?

Thanks for the response

Ray Laubert SSCrazy Points: 2910 More actions · Answer 3

unfortunately, the commands that were used to create the database (ie History) are stored in Master. This is what you are seeing when you query the design. The only way I know would be to drop and recreate the database.

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

newbie_dev SSCommitted Points: 1916 More actions · Answer 4

Ok

I really didn't know how to deal with this, and I'm glad I know the answer

Thanks a lot