Do primary key have 'not for replication' option

Question

Hi,

Do primary key has 'NOT FOR REPLICATION' option(just like foreign key, trigger)?

Thanks and regards,

Wallace

Answer 1

yes if you are using say identity column for primary key say in peer to peer replication you could use NOT FOR REPLICATION

http://technet.microsoft.com/en-us/library/ms152543.aspx

Answer 2

yes if you are using say identity column for primary key say in peer to peer replication you could use NOT FOR REPLICATION

http://technet.microsoft.com/en-us/library/ms152543.aspx

But add to where from the sql clause?

Our sql for p.k. is

ALTER TABLE [dbo].[AccessControl] ADD  CONSTRAINT [PK_AccessControl] PRIMARY KEY CLUSTERED
(
    [Idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Thanks and regards,

Wallace

Answer 3

Is that column(PRIMARY KEY) NOT an identity column?  I believe you cannot set it NOT FOR REPLICATION if its NOT an auto generated primary column, as the data needs to be pushed to subscriber.(but i will let someone else confirm it , as i haven't done/tested it)

If its an identity column you could set it like this

create table PkTest(ID int IDENTITY(1,1)  NOT FOR REPLICATION,Name varchar(255),CONSTRAINT PK_ID PRIMARY KEY(ID))
GO

Or 

create table PkTest(ID int IDENTITY(1,1)  NOT FOR REPLICATION,Name varchar(255))
GO
ALTER TABLE PKTest ADD CONSTRAINT PK_ID PRIMARY KEY(ID)
GO

Answer 4

Is that column(PRIMARY KEY) NOT an identity column?  I believe you cannot set it NOT FOR REPLICATION if its NOT an auto generated primary column, as the data needs to be pushed to subscriber.(but i will let someone else confirm it , as i haven't done/tested it)

If its an identity column you could set it like this

create table PkTest(ID int IDENTITY(1,1)  NOT FOR REPLICATION,Name varchar(255),CONSTRAINT PK_ID PRIMARY KEY(ID))
GO

Or 

create table PkTest(ID int IDENTITY(1,1)  NOT FOR REPLICATION,Name varchar(255))
GO
ALTER TABLE PKTest ADD CONSTRAINT PK_ID PRIMARY KEY(ID)
GO

Yes, it's an identity field. From the above sql it means that the 'NOT FOR REPLICATION' is set to identity column field, not on primary key or check constraint. Thanks a lot.

Best regards,

Wallace