I was setting up replication and discovered that explicitly defined primary
keys are required on the tables being replicated. We currenty have defined
these on exactly zero of our tables (legacy thing - you get the picture). I
told the developers I needed them. They asked me if it would break anything
if we put them on. I'm thinking that as long as we don't define explicit
PK/FK relationshiops bewteen the tables we should be OK.
I would appreciate any insight on the matter.
Bob Castleman
DBA PoseurOne idea would be to add a UniqueIdentifier field and set it to be the
RowGuidCol and the PK (along with a default of NewId()). By doing this, you
avoid SQL adding this column for you anyway for replication.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>I was setting up replication and discovered that explicitly defined primary
>keys are required on the tables being replicated. We currenty have defined
>these on exactly zero of our tables (legacy thing - you get the picture). I
>told the developers I needed them. They asked me if it would break anything
if
>we put them on. I'm thinking that as long as we don't define explicit PK/FK
>relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>|||We already have unique IDs on the tables, they were just never explicitly
defined within SQL Server as PKs. We may at some point start defining PK/FK
relationships and referential integrity so wouldn't make sense to use our
current IDs? I am more concerned about unintended side effects. I can't see
how defining a PK on table would cause a problem, but I need to make sure.
Bob
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
> One idea would be to add a UniqueIdentifier field and set it to be the
> RowGuidCol and the PK (along with a default of NewId()). By doing this,
> you avoid SQL adding this column for you anyway for replication.
>
> Thomas
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>|||As long as the data is actually unique, it shouldn't be a problem.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining
> PK/FK relationships and referential integrity so wouldn't make sense to
> use our current IDs? I am more concerned about unintended side effects. I
> can't see how defining a PK on table would cause a problem, but I need to
> make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
declare @.sql nvarchar(4000)
declare @.s datetime
declare @.e datetime
declare @.i int
declare @.datepart varchar(15)
set @.s = '2005-01-01T12:00:00.000'
set @.e = '2005-01-01T13:00:00.000'
set @.i = 15
set @.datepart = 'minute'
set @.sql = N'
select
right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
from
number as n
where
number % ' + ltrim(@.i) + N' = 0
and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 126)
+ N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
print @.sql
exec sp_executesql @.sql
go
drop table number
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Bob Castleman" wrote:
> I was setting up replication and discovered that explicitly defined primar
y
> keys are required on the tables being replicated. We currenty have defined
> these on exactly zero of our tables (legacy thing - you get the picture).
I
> told the developers I needed them. They asked me if it would break anythin
g
> if we put them on. I'm thinking that as long as we don't define explicit
> PK/FK relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Why should I consider using an auxiliary numbers table?
> http://www.aspfaq.com/show.asp?id=2516
> use northwind
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a cross join sysobjects as b
> go
>
> declare @.sql nvarchar(4000)
> declare @.s datetime
> declare @.e datetime
> declare @.i int
> declare @.datepart varchar(15)
> set @.s = '2005-01-01T12:00:00.000'
> set @.e = '2005-01-01T13:00:00.000'
> set @.i = 15
> set @.datepart = 'minute'
> set @.sql = N'
> select
> right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
> convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
> from
> number as n
> where
> number % ' + ltrim(@.i) + N' = 0
> and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 12
6)
> + N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
> print @.sql
> exec sp_executesql @.sql
> go
> drop table number
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Bob Castleman" wrote:
>|||If that's the case, then just declare those columns as the PK. You can decla
re a
PK without having to declare FK (although it helps with data integrity).
The only problems you might encounter by setting a given column(s) as the PK
are:
1. If the data is not unique or contains nulls
or
2. If the the app that writes the data expects that it can fill in duplicate
or
null data on one pass even if it changes it to be non-nullable and unique in
another pass. Obviously, this won't work as SQL will prevent any nulls or
duplciate values from ever being written.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining PK/F
K
> relationships and referential integrity so wouldn't make sense to use our
> current IDs? I am more concerned about unintended side effects. I can't se
e
> how defining a PK on table would cause a problem, but I need to make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>
If at all possible, just add the PK.