June 26, 2013 at 4:28 am
Howdy,
I've been checking the foreign keys in my system for whether they are trusted in order to leverage the optimiser being able to effectively ignore joins if no column is return from the referenced table.
However, I can't seem to get them to work if the foreign key is replicated. If I run the below command, the foreign key is successfully created:-
CREATE TABLE [dbo].[zUser]
(
[UserId] [int] IDENTITY(1,1) NOT NULL,
[Username] [varchar](50) NOT NULL,
[Firstname] [varchar](50) NOT NULL,
[Surname] [varchar](50) NOT NULL
CONSTRAINT [pk_zUser] PRIMARY KEY CLUSTERED ([UserId] ASC)
)
CREATE TABLE [dbo].[Debtor]
(
[DebtorId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CreatedBy] [int] NOT NULL
CONSTRAINT [pk_Debtor] PRIMARY KEY CLUSTERED ([DebtorId] ASC)
)
ALTER TABLE [dbo].[Debtor] WITH CHECK ADD CONSTRAINT [FK_Debtor_zUserCreatedBy] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[zUser] ([UserId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Debtor] WITH CHECK CHECK CONSTRAINT [FK_Debtor_zUserCreatedBy]
GO
However, checking the sys.foreign_keys table shows that the foreign key is not trusted. In this instance I need to replicate the Debtor table to a read only server, but the zUser table is superfluous to requirements on that server. Ideally I don't want to bother replicating it at all, but I'm worried that I now have a foreign key that isn't being checked.
I also can't run the Foreign Key creation without the NOT FOR REPLICATION option because it then screws up the replication. If I drop the article for the table, recreate the foreign key WITH CHECK and without the NOT FOR REPLICATION, then recreate the article and don't include foreign key constraints it seems to work. I'd prefer not to do this unless I absolutely have to though, due to the downtime this will cause.
I've found this article stating it was a bug way back when, but has since been fixed. Is this maybe not true?
Cheers,
Matthew
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 26, 2013 at 7:44 am
Ok, seem to have a work around; create the foreign key with NOCHECK but no NOT FOR REPLICATION, then run the alter statement with check eg:-
ALTER TABLE [dbo].[Debtor] WITH NOCHECK ADD CONSTRAINT [FK_Debtor_zUserCreatedBy] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[zUser] ([UserId])
GO
ALTER TABLE [dbo].[Debtor] WITH CHECK CHECK CONSTRAINT [FK_Debtor_zUserCreatedBy]
GO
If the table is already replicated, it doesn't create the foreign key on the replicated table if the referenced object doesn't exist, it just seems to ignore it.
If the referenced table does exist, it creates it but with the NOCHECK option, and the subsequent ALTER statement to check the constraint is ignored.
Seems a little counter intuitive, but at least it's working.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply