PrimaryKey and unique

how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
Looks like you have it almost right:
alter table MyTable
add
constraint PK_MyTable primary key (ClientID, UserID)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Michael Haberichter" <Haberichter@.community.nospam> wrote in message
news:ED15E772-0327-4C98-8FBD-92AE3717D1FB@.microsoft.com...
how can I design a table to get unique values in a column within the PK
columns?
Example:
Client (int, PK)
User (int, PK
Userid(char...)
userid's shoud be unique for a client. Other clients are allowed to have
same userid's as other clients. A second user of a client cannot have the
same userid as other user of this client.
thanks for any help.
sql

primary keys

I am using the IDENTITY feature to generate the values fo rmy primary keys. I understand that primary keys need to be unique.

If I undestand correctly then they need to be uniquely identified in all table with in the sam database.

I am not sure if when I create a primary key using this feature. Do I need to change the seed and increment values in order to have primary keys for each table that are truely unique? This seems like the way to make all of the PK values in a database unique.

-Thanks to anyone that may be able to help

First off, a reminder that just setting an identity column doesn't make a row unique to the user. Having values:

Identity Name

1 Fred

2 Fred

Will make the two rows indistiguishable. So an Alternate Key on Name would make this far more ideal.

And yes, if you truly need your integer identity index keys to be unique in a database, a combination of seed and a check constraint will work:

create table first
(
firstId int identity(1,1) check (firstId between 1 and 100)
)
create table second
(
secondId int identity(101,1) check (secondId between 101 and 200)
)
go
insert into first default values;
insert into first default values;
insert into second default values;
insert into second default values;

select *

from first

select *

from second

firstId

--

1

2

secondId

--

101

102

Another method is to just use a guid for your keys. Of course guids are much larger and unwieldly to work with, but they are another way to go.

A big question is why do you need the values to be unique in the database? Just having them unique in the table they are in is generally good enough. It will definitely make it easier to work with since you won't have to be concerned with ranges of values later, especially if the data grows greater than initially expected.

|||

Primary key is the Domain Integrity Constraint. Here the Domain means a single table. Primary key insures the uniqueness of the table (single domain). It never validates other tables. Practically your understanding about PK is not possible. Bcs the number of records in each table is non-deterministic values. You can't make any assumption to accommodate this. Think about performance if i have more tables in my database.

But, UNIQUEIDENTIFIER typed values are used to identify the unique value across the table/database/server. Since it use the GUID on every generation the GUID must be unique.

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks

|||

Thanks for the info. I need to do some studying on domain constraints and the different things that can be used to maintain integrity in this type of situation.

-Thanks for your help

Primary Key

Hello,
I'm trying to set a transactional replication. Everything is working very
well, but i don't know why primary key and default values are not transfered
to the subscribers.
Any idea !!!!!!!
Thanks
Matthew,
have a look at the article properties (elipsis button). You can select to
transfer DRI, in which case the PKs will be created at the subscriber,
otherwise there is a unique index created instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks a lot for your answer.
I'm very new in using replication. I didn't know exactly what you meant with
DRI, but i repeated the whole process and checked the article properties. I
have Still the same problem. Everything is transfered to the subscribers,
even indexes, but when i right-click on my table and select the "Design
Table", I can not see that "Primary Key" icon beside my key and none of my
"Default Values" is set.
Even in that "Default Table Article Properties" wizard, I see that there is
written "Indexes for primary keys are always copied", and it doesn't even let
you mark or unmark it, but still it doesn't work.
Please help !!!!!!!!!!!!!!
THanks again,
Matthew
"Paul Ibison" wrote:

> Matthew,
> have a look at the article properties (elipsis button). You can select to
> transfer DRI, in which case the PKs will be created at the subscriber,
> otherwise there is a unique index created instead.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Matthew,
have a look at the article properties (elipsis button on the publication
properties). On the snapshot tab, select the 'Include declared referential
integrity' option. After that, you'll need to reinitialize to have the whole
table definition go down to the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks a lot for your help. It worked this time. After your first answer i
selected all those options on the snapshot tab. I'm positive that i did it,
but unbelievably, after your second answer I wanted to double check it that i
saw only this one ,"Include Declared Referential Integrity", was unmarked.
Anyways, It's fine now.
I have another problem now. I don't know why after transfering stored
procedures it puts SP names in a "" at the subscribers.
Thanks again,
Matthew
"Paul Ibison" wrote:

> Matthew,
> have a look at the article properties (elipsis button on the publication
> properties). On the snapshot tab, select the 'Include declared referential
> integrity' option. After that, you'll need to reinitialize to have the whole
> table definition go down to the subscriber.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

previous week date parameters

Hi,
Hope someone can help. I am trying to get the default value date parameters
for the previous working week. I then want to use these values while running
the report during the following week until the next Monday. I have almost got
it working with the following:
I put the following code in 'Report Properties>Code':
Public Shared Function GetDate(Byval value as Double)
value=(value)- Weekday(Now)
GetDate=DateAdd("d", value, Now)
End Function
and then the following parameters:
Date from: =Code.GetDate(-5)
Date to: =Code.GetDate(-1)
The only issue i have with this is that it brings in the following:
Date from: 08/11/2004 14:40:54
Date to: 12/11/2004 14:40:54
I don't want it to include the time though. Just the following:
Date from: 08/11/2004 00:00:00
Date to: 12/11/2004 00:00:00
Does anybody know how to get around this?
Any help would be appreciated.
PaulTo convert the date I use .ToShortDateString()
Example:
=Today.AddDays(-1).ToShortDateString()
"pcalv" wrote:
> Hi,
> Hope someone can help. I am trying to get the default value date parameters
> for the previous working week. I then want to use these values while running
> the report during the following week until the next Monday. I have almost got
> it working with the following:
> I put the following code in 'Report Properties>Code':
> Public Shared Function GetDate(Byval value as Double)
> value=(value)- Weekday(Now)
> GetDate=DateAdd("d", value, Now)
> End Function
> and then the following parameters:
> Date from: =Code.GetDate(-5)
> Date to: =Code.GetDate(-1)
> The only issue i have with this is that it brings in the following:
> Date from: 08/11/2004 14:40:54
> Date to: 12/11/2004 14:40:54
> I don't want it to include the time though. Just the following:
> Date from: 08/11/2004 00:00:00
> Date to: 12/11/2004 00:00:00
> Does anybody know how to get around this?
> Any help would be appreciated.
> Paul
>|||Thanks for the reply. Unfortunately this does not work with the custom code i
am using.
Any other help would be appreciated.
Paul
"datobin1" wrote:
> To convert the date I use .ToShortDateString()
> Example:
> =Today.AddDays(-1).ToShortDateString()
>
> "pcalv" wrote:
> > Hi,
> >
> > Hope someone can help. I am trying to get the default value date parameters
> > for the previous working week. I then want to use these values while running
> > the report during the following week until the next Monday. I have almost got
> > it working with the following:
> >
> > I put the following code in 'Report Properties>Code':
> >
> > Public Shared Function GetDate(Byval value as Double)
> > value=(value)- Weekday(Now)
> > GetDate=DateAdd("d", value, Now)
> > End Function
> >
> > and then the following parameters:
> >
> > Date from: =Code.GetDate(-5)
> > Date to: =Code.GetDate(-1)
> >
> > The only issue i have with this is that it brings in the following:
> >
> > Date from: 08/11/2004 14:40:54
> > Date to: 12/11/2004 14:40:54
> >
> > I don't want it to include the time though. Just the following:
> >
> > Date from: 08/11/2004 00:00:00
> > Date to: 12/11/2004 00:00:00
> >
> > Does anybody know how to get around this?
> >
> > Any help would be appreciated.
> >
> > Paul
> >
> >

previous value in trigger

Hi,
Can I get previous and current values of row in trigger for update
operation.
Regards,
Shah Adarsh.The previous value is in the deleted table and the current value in the
inserted table.
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140679727.735390.38640@.i39g2000cwa.googlegroups.com...
> Hi,
> Can I get previous and current values of row in trigger for update
> operation.
>
> Regards,
> Shah Adarsh.
>|||The data is stored in the virtual INSERTED table:
Operation --> Virtual Tables
INSERT --> INSERTED
DELETE --> DELETED
UPDATE --> INSERTED,DELETED
HTH, jens Suessmeyer.|||Thanks Quinn. It solved my problem.|||Thanks Jens. It solved my problem.