|
|
You're in the
sql server replication
group:Change Unique ID to non unique ID in replicated table
sql server replication:
Exactly what do you mean by non-unique. There are three kinds of indexes,
clustered, non clustered or unique. Unique means an index or actually a constraint that uses an index, which ensures unique values and allows a null. A non-clustered index is an index that is maintained on a seperate database page containing pointers which point back to where in the table a particular value occurs. A clustered index orders the data in the table according to a criteria. To change an index you do this sp_help tablename and obtain the index name then drop the index drop index authors.aunmind then recreate it (if you want to convert the index to a clustered or non-clustered index). I assume you are talking about the table on the subscriber. If you want to replicate a primary key which occurs on a published table on the publisher to a primary key on the subscriber right click on yoru publication, select properties, click the articles tab, and click the three ellipses next to your article you wish to modify the index on. Then in the snapshot tab, check include DRI. By default primary keys are replicated as unique indexes on the subscriber. HTH -- Hilary Cotter Looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Vivek" <vivek@nospam.com> wrote in message news:uMeeotwjEHA.2908@TK2MSFTNGP10.phx.gbl... > Hi, > > I have a replicated to table. It has the ID column as unique and I want it > to convert it into non unique. How can I do it? > >
Hi,
I have a replicated to table. It has the ID column as unique and I want it to convert it into non unique. How can I do it?
You will have to alter the table to drop the constraint
create table primarykey (pk int not null identity(1,1) primary key) GO sp_help primarykey GO --scroll down until you see the name of the primary key - in our case its called PK__primarykey__52AE4273 alter table primarykey drop constraint PK__primarykey__52AE4273 GO -- Hilary Cotter Looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html [quoted text, click to view] "Vivek Sharma" <joebloggs@news.com> wrote in message news:OV7xLN9jEHA.2412@TK2MSFTNGP15.phx.gbl... > Hi Hillary, > > Thanks for your reply. I think I should have framed my query differently. > I have a table on publisher with a primary key. What we intend to do is get > rid of the primary key so that we can insert any number and the following > records. Now, how do I can I change the column to be not primary key. > > Thanks > > Vivek > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23Dv3VS1jEHA.1520@TK2MSFTNGP10.phx.gbl... > > Exactly what do you mean by non-unique. There are three kinds of indexes, > > clustered, non clustered or unique. > > > > Unique means an index or actually a constraint that uses an index, which > > ensures unique values and allows a null. A non-clustered index is an index > > that is maintained on a seperate database page containing pointers which > > point back to where in the table a particular value occurs. A clustered > > index orders the data in the table according to a criteria. > > > > To change an index you do this > > > > sp_help tablename and obtain the index name > > > > then drop the index > > > > drop index authors.aunmind > > > > then recreate it (if you want to convert the index to a clustered or > > non-clustered index). > > > > I assume you are talking about the table on the subscriber. > > > > If you want to replicate a primary key which occurs on a published table > on > > the publisher to a primary key on the subscriber right click on yoru > > publication, select properties, click the articles tab, and click the > three > > ellipses next to your article you wish to modify the index on. Then in the > > snapshot tab, check include DRI. By default primary keys are replicated as > > unique indexes on the subscriber. > > > > HTH > > > > -- > > Hilary Cotter > > Looking for a book on SQL Server replication? > > http://www.nwsu.com/0974973602.html > > > > > > "Vivek" <vivek@nospam.com> wrote in message > > news:uMeeotwjEHA.2908@TK2MSFTNGP10.phx.gbl... > > > Hi, > > > > > > I have a replicated to table. It has the ID column as unique and I want > > it > > > to convert it into non unique. How can I do it? > > > > > > > > > > > >
Hi Hillary,
Thanks for your reply. I think I should have framed my query differently. I have a table on publisher with a primary key. What we intend to do is get rid of the primary key so that we can insert any number and the following records. Now, how do I can I change the column to be not primary key. Thanks Vivek [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23Dv3VS1jEHA.1520@TK2MSFTNGP10.phx.gbl... > Exactly what do you mean by non-unique. There are three kinds of indexes, > clustered, non clustered or unique. > > Unique means an index or actually a constraint that uses an index, which > ensures unique values and allows a null. A non-clustered index is an index > that is maintained on a seperate database page containing pointers which > point back to where in the table a particular value occurs. A clustered > index orders the data in the table according to a criteria. > > To change an index you do this > > sp_help tablename and obtain the index name > > then drop the index > > drop index authors.aunmind > > then recreate it (if you want to convert the index to a clustered or > non-clustered index). > > I assume you are talking about the table on the subscriber. > > If you want to replicate a primary key which occurs on a published table on > the publisher to a primary key on the subscriber right click on yoru > publication, select properties, click the articles tab, and click the three > ellipses next to your article you wish to modify the index on. Then in the > snapshot tab, check include DRI. By default primary keys are replicated as > unique indexes on the subscriber. > > HTH > > -- > Hilary Cotter > Looking for a book on SQL Server replication? > http://www.nwsu.com/0974973602.html > > > "Vivek" <vivek@nospam.com> wrote in message > news:uMeeotwjEHA.2908@TK2MSFTNGP10.phx.gbl... > > Hi, > > > > I have a replicated to table. It has the ID column as unique and I want > it > > to convert it into non unique. How can I do it? > > > > > >
Thanks a lot Hilary. It was a great help.
Vivek [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:elqZKECkEHA.384@TK2MSFTNGP10.phx.gbl... > You will have to alter the table to drop the constraint > > create table primarykey > (pk int not null identity(1,1) primary key) > GO > sp_help primarykey > GO > --scroll down until you see the name of the primary key - in our case its > called PK__primarykey__52AE4273 > alter table primarykey > drop constraint PK__primarykey__52AE4273 > GO > > -- > Hilary Cotter > Looking for a book on SQL Server replication? > http://www.nwsu.com/0974973602.html > > > "Vivek Sharma" <joebloggs@news.com> wrote in message > news:OV7xLN9jEHA.2412@TK2MSFTNGP15.phx.gbl... > > Hi Hillary, > > > > Thanks for your reply. I think I should have framed my query differently. > > I have a table on publisher with a primary key. What we intend to do is > get > > rid of the primary key so that we can insert any number and the following > > records. Now, how do I can I change the column to be not primary key. > > > > Thanks > > > > Vivek > > > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > > news:%23Dv3VS1jEHA.1520@TK2MSFTNGP10.phx.gbl... > > > Exactly what do you mean by non-unique. There are three kinds of > indexes, > > > clustered, non clustered or unique. > > > > > > Unique means an index or actually a constraint that uses an index, which > > > ensures unique values and allows a null. A non-clustered index is an > index > > > that is maintained on a seperate database page containing pointers which > > > point back to where in the table a particular value occurs. A clustered > > > index orders the data in the table according to a criteria. > > > > > > To change an index you do this > > > > > > sp_help tablename and obtain the index name > > > > > > then drop the index > > > > > > drop index authors.aunmind > > > > > > then recreate it (if you want to convert the index to a clustered or > > > non-clustered index). > > > > > > I assume you are talking about the table on the subscriber. > > > > > > If you want to replicate a primary key which occurs on a published table > > on > > > the publisher to a primary key on the subscriber right click on yoru > > > publication, select properties, click the articles tab, and click the > > three > > > ellipses next to your article you wish to modify the index on. Then in > the > > > snapshot tab, check include DRI. By default primary keys are replicated > as > > > unique indexes on the subscriber. > > > > > > HTH > > > > > > -- > > > Hilary Cotter > > > Looking for a book on SQL Server replication? > > > http://www.nwsu.com/0974973602.html > > > > > > > > > "Vivek" <vivek@nospam.com> wrote in message > > > news:uMeeotwjEHA.2908@TK2MSFTNGP10.phx.gbl... > > > > Hi, > > > > > > > > I have a replicated to table. It has the ID column as unique and I > want > > > it > > > > to convert it into non unique. How can I do it? > > > > > > > > > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |