Primary keys ...

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...
>

Primary Keys

I have managed to confuse myself as to when and how you can set up Primary
Keys on an MSDE database.
Are Primary Keys only used when you are connected to an MS Access project or
can they be used in other places.
I come from an MS Access background and am familure with the use of Primary
Keys my problem is that I cannot seem to turn them on. I already have a
clustered index set up on the table and am using Identidy to declare a field
as unique. Could this be the reason why I cannot turn on the Primary Key
option?
Any insight would be useful.
Thanks
June
> Keys my problem is that I cannot seem to turn them on.
What does this mean? How are you trying to "turn them on"? What tool are
you using, and are you using ALTER TABLE or clicking buttons in a GUI? Are
you getting an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
|||I have tried using Andrea Montanari's DbaMGR2k and Microsoft Web Data
Administrator.
Using DbaMGR2k - When logged on as dbo I cannot check the checkbox beside
Primary Key although I can change all the other field settings. There is no
message, simply nothing happens.
Using Microsoft Web Data - when editing a table I can set the Primary Key
only on tables that have no rows of data in them. As soon as there is data
in the table the option to edit the fields is removed.
I want to be able to set up Primary Keys and Foreign Keys in order to set up
a relationship. I also want my tables to have a clustered index which in
most, but not all, cases will be the same field as the Primary Key.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OraGgF%23cEHA.3512@.TK2MSFTNGP12.phx.gbl...
> What does this mean? How are you trying to "turn them on"? What tool are
> you using, and are you using ALTER TABLE or clicking buttons in a GUI?
Are
> you getting an error message? If so, what is it?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||If you can use osql or isql to connect to MSDE, you can issue an ALTER TABLE
/ ADD CONSTRAINT command, which isn't limited by what an application thinks
you should/shouldn't be able to do. Barring that, you can create a dummy
table with the primary key intact, copy the data over, drop the old table,
and rename the new table. If these tools will let you do those things too,
of course.
http://www.aspfaq.com/
(Reverse address to reply.)
"June Macleod" <junework@.hotmail.com> wrote in message
news:#xwOW39cEHA.1652@.TK2MSFTNGP09.phx.gbl...
> I have managed to confuse myself as to when and how you can set up Primary
> Keys on an MSDE database.
> Are Primary Keys only used when you are connected to an MS Access project
or
> can they be used in other places.
> I come from an MS Access background and am familure with the use of
Primary
> Keys my problem is that I cannot seem to turn them on. I already have a
> clustered index set up on the table and am using Identidy to declare a
field
> as unique. Could this be the reason why I cannot turn on the Primary Key
> option?
> Any insight would be useful.
> Thanks
> June
>
|||hi June,
"June Macleod" <junework@.hotmail.com> ha scritto nel messaggio
news:eKX7Vb%23cEHA.1644@.tk2msftngp13.phx.gbl...
> I have tried using Andrea Montanari's DbaMGR2k and Microsoft Web Data
> Administrator.
> Using DbaMGR2k - When logged on as dbo I cannot check the checkbox beside
> Primary Key although I can change all the other field settings. There is
no
> message, simply nothing happens.
as regard DbaMgr2k, the help file specifies the Primary Key can not be set
in the "Table Management" window, where the check is read only, but in the
Indexes/Keys/Check window =;-D
so you have to access that window, both for PKs and indexes management as
for Foreign Keys management, as DbaMgr2k does not implement Diagrams
management [ =:-( , I know ]

> Using Microsoft Web Data - when editing a table I can set the Primary Key
> only on tables that have no rows of data in them. As soon as there is
data
> in the table the option to edit the fields is removed.
yes, you are right... WDA will not allow you to edit existing columns when
the base table is populated (sqlwish@.microsoft.com =;-D )

> I want to be able to set up Primary Keys and Foreign Keys in order to set
up
> a relationship. I also want my tables to have a clustered index which in
> most, but not all, cases will be the same field as the Primary Key.
and you can do it... with WDA you have to resort on executing Transact-SQL
statements, becouse it only supports PK setting (AFAIK)... with my tool you
can do it both graphically (but not in the Table Management window =;-D)
and/or performing Transact-SQL statements execution...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> as regard DbaMgr2k, the help file specifies the Primary Key can not be set
> in the "Table Management" window, where the check is read only, but in the
> Indexes/Keys/Check window =;-D
> so you have to access that window, both for PKs and indexes management as
> for Foreign Keys management, as DbaMgr2k does not implement Diagrams
> management [ =:-( , I know ]
>
Thank you, that works
June

primary keys

Hello,
I've a problem setting up a constraint on a table. There are two primary
keys in that table and one of them will be filled in through an
access application, wether it be nothing or some integer value.
What i want to do is to set a zero-value in a primary key column, how
can i do this? I've tried coalesce([columnname],'',0), but that gives me
an error. I think the syntax for the constraint is wrong, but i don't
know how to set it correctly.
Can anyone help me?Jason
You cannot do that . It seems you need create an UNIQUE CONTRAINT which will
accept a NULL value
Look at this example written by Steve Kass
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?|||There can only be one designated primary key for a table; any additional
unique keys are called candidate keys. Column(s) with primary key constraint
cannot have NULL value and all values must be unique.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?sql