locked
Do primary key have 'not for replication' option RRS feed

  • Question

  • Hi,

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

    Thanks and regards,

    Wallace

    Sunday, April 15, 2012 9:05 AM

Answers

  • 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




    • Edited by SQL_Jay Sunday, April 15, 2012 12:34 PM
    • Marked as answer by Wallace Chan Sunday, April 15, 2012 1:12 PM
    Sunday, April 15, 2012 12:33 PM

All replies

  • 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

    Sunday, April 15, 2012 11:47 AM
  • 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

    Sunday, April 15, 2012 11:54 AM
  • 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




    • Edited by SQL_Jay Sunday, April 15, 2012 12:34 PM
    • Marked as answer by Wallace Chan Sunday, April 15, 2012 1:12 PM
    Sunday, April 15, 2012 12:33 PM
  • 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

    Sunday, April 15, 2012 1:12 PM