What will happen when NOT FOR REPLICATION is specified on a constraint

Problem: What will happen when NOT FOR REPLICATION is specified on a constraint

Solution:

If a table is published using replication, foreign key constraints are automatically disabled for operations performed by replication agents. When a replication agent performs an insert, update, or delete at a Subscriber, the constraint is not checked; if a user performs an insert, update, or delete, the constraint is checked. The constraint is disabled for the replication agent because the constraint was already checked at the Publisher when the data was originally inserted, updated, or deleted.

Example:

In this article, I am going to show we can create a foreign key constraint with not for replication.

CREATE TABLE dbo.t1 (

c1 INT NOT NULL PRIMARY KEY

);

CREATE TABLE dbo.t2 (

c1 INT NOT NULL IDENTITY PRIMARY KEY,

c2 INT NOT NULL);

GO

ALTER TABLE dbo.t2 WITH CHECK

ADD CONSTRAINT FK_t2_t1 FOREIGN KEY (c2)

REFERENCES dbo.t1(c1)

NOT FOR REPLICATION

GO

OUTPUT:

Note: After NOT FOR REPLICATION  is specified it will convert the constraint is converted into to no check and it will not check for existing data in the table, which makes it not trusted.

SELECT [name], type_desc, is_disabled, is_not_trusted,is_not_for_replication

FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID('dbo.t2');

GO

OUTPUT:

name	type_desc	is_disabled	is_not_trusted	is_not_for_replication
FK_t2_t1	FOREIGN_KEY_CONSTRAINT	0	1	1