Unique Constraint in MS ACCESS

Hi,
i m very new to MS Access but i m familier with other
dataabses.

Now in my application i m going to create a table and its
data is updated every time user is connected to the net.in
such to avoid the data replication is tehre any way to set
unique constraint in the table, if so please let me know,
i m using VB as the Front end.

Thanks in advance

Two ways, depending on what you're trying to achieve. You can create a
unique index, specifying whatever combination of fields is appropriate, or
you can create an Autonumber field, which will guarantee the uniqueness of
each record.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

"rajups" wrote in message
...
Hi,
i m very new to MS Access but i m familier with other
dataabses.

Now in my application i m going to create a table and its
data is updated every time user is connected to the net.in
such to avoid the data replication is tehre any way to set
unique constraint in the table, if so please let me know,
i m using VB as the Front end.

Thanks in advance

you can create an Autonumber field, which will guarantee the uniqueness of
each record.

How will that guarantee uniqueness? It will only guarantee that the
Autonumber field is unique.

73863838, Duplicate Data
98597368, Duplicate Data
69409893, Duplicate Data

Only if one cannot define the natural key should one resort to a surrogate,
and probably best not to be Autonumber if it is to be exposed to the user.

If one can define a natural key but insist, for some obscure reason, to add
an AutoNumber then the natural key should be defined as Unique, Required and
Not Null.

--
Slainte

Craig Alexander Morrison


"Rebecca Riordan" wrote in message
...
Two ways, depending on what you're trying to achieve. You can create a
unique index, specifying whatever combination of fields is appropriate, or
you can create an Autonumber field, which will guarantee the uniqueness of
each record.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

"rajups" wrote in message
...
Hi,
i m very new to MS Access but i m familier with other
dataabses.

Now in my application i m going to create a table and its
data is updated every time user is connected to the net.in
such to avoid the data replication is tehre any way to set
unique constraint in the table, if so please let me know,
i m using VB as the Front end.

Thanks in advance

Ok, I'm about to step between two opposing views and will probably get
clobbered from both sides but, oh well.

How will that guarantee uniqueness? It will only guarantee that the
Autonumber field is unique.

73863838, Duplicate Data
98597368, Duplicate Data
69409893, Duplicate Data

I agree with you completely here, Craig. It is possible to go on ad
infinitum adding duplicate records, except for the Autonumber field. (gd&r
from my friend Rebecca, promising to bake her a very special loaf of bread)

If one can define a natural key but insist, for some obscure reason, to
add
an AutoNumber then the natural key should be defined as Unique, Required
and
Not Null.

Actually, though, the reasons for doing so are not quite so obscure. First,
and probably most importantly, a surrogate key provides an easier way to
create joins between related tables. Trying to build a join on a long
natural key can be pretty unwieldly. Second, most all of us have encountered
those times when finding a natural key would require including almost, if
not entirely, every field in the table in the primary key. There are some
tables where I have yet to be convinced that it is even possible to find a
completely fool proof natural key -- one that can never be duplicated. In
those situations, also, not even a unique constraint will work. Some other
method of preventing duplication is required -- such as a check that gives
the user the final choice of allowing the duplicate to be committed to the
database or not.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Craig Alexander Morrison" wrote in message
...
you can create an Autonumber field, which will guarantee the uniqueness
of
each record.

How will that guarantee uniqueness? It will only guarantee that the
Autonumber field is unique.

73863838, Duplicate Data
98597368, Duplicate Data
69409893, Duplicate Data

Only if one cannot define the natural key should one resort to a
surrogate,
and probably best not to be Autonumber if it is to be exposed to the user.

If one can define a natural key but insist, for some obscure reason, to
add
an AutoNumber then the natural key should be defined as Unique, Required
and
Not Null.

--
Slainte

Craig Alexander Morrison


"Rebecca Riordan" wrote in message
...
Two ways, depending on what you're trying to achieve. You can create a
unique index, specifying whatever combination of fields is appropriate,
or
you can create an Autonumber field, which will guarantee the uniqueness
of
each record.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

"rajups" wrote in message
...
Hi,
i m very new to MS Access but i m familier with other
dataabses.

Now in my application i m going to create a table and its
data is updated every time user is connected to the net.in
such to avoid the data replication is tehre any way to set
unique constraint in the table, if so please let me know,
i m using VB as the Front end.

Thanks in advance

Oh, my apologies. Sloppy thinking on my part. Of course a unique
identifier is not the same thing as a unique record. (Dirk Goldgar, where
are you when I need you? eg)

And Lynn, while I agree with your example, I would argue that if you can't
reliably distinguish between two records, then for the purposes of the
system (and only for the purposes of the system), they're the same entity.
(Which for some reason reminds me of a recent remark of my distinguished
father's: "I agree with you completely, but I'm wrong.")

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

"Lynn Trapp" wrote in message
...
Ok, I'm about to step between two opposing views and will probably get
clobbered from both sides but, oh well.

How will that guarantee uniqueness? It will only guarantee that the
Autonumber field is unique.

73863838, Duplicate Data
98597368, Duplicate Data
69409893, Duplicate Data

I agree with you completely here, Craig. It is possible to go on ad
infinitum adding duplicate records, except for the Autonumber field. (gd&r
from my friend Rebecca, promising to bake her a very special loaf of
bread)

If one can define a natural key but insist, for some obscure reason, to
add
an AutoNumber then the natural key should be defined as Unique, Required
and
Not Null.

Actually, though, the reasons for doing so are not quite so obscure.
First,
and probably most importantly, a surrogate key provides an easier way to
create joins between related tables. Trying to build a join on a long
natural key can be pretty unwieldly. Second, most all of us have
encountered
those times when finding a natural key would require including almost, if
not entirely, every field in the table in the primary key. There are some
tables where I have yet to be convinced that it is even possible to find a
completely fool proof natural key -- one that can never be duplicated. In
those situations, also, not even a unique constraint will work. Some other
method of preventing duplication is required -- such as a check that gives
the user the final choice of allowing the duplicate to be committed to the
database or not.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Craig Alexander Morrison" wrote in message
...
you can create an Autonumber field, which will guarantee the
uniqueness
of
each record.

How will that guarantee uniqueness? It will only guarantee that the
Autonumber field is unique.

73863838, Duplicate Data
98597368, Duplicate Data
69409893, Duplicate Data

Only if one cannot define the natural key should one resort to a
surrogate,
and probably best not to be Autonumber if it is to be exposed to the
user.

If one can define a natural key but insist, for some obscure reason, to
add
an AutoNumber then the natural key should be defined as Unique, Required
and
Not Null.

--
Slainte

Craig Alexander Morrison


"Rebecca Riordan" wrote in message
...
Two ways, depending on what you're trying to achieve. You can create
a
unique index, specifying whatever combination of fields is
appropriate,
or
you can create an Autonumber field, which will guarantee the
uniqueness
of
each record.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

"rajups" wrote in message
...
Hi,
i m very new to MS Access but i m familier with other
dataabses.

Now in my application i m going to create a table and its
data is updated every time user is connected to the net.in
such to avoid the data replication is tehre any way to set
unique constraint in the table, if so please let me know,
i m using VB as the Front end.

Thanks in advance

Lynn,

What is the problem with a compound index? If a table's primary key consists
of 8 fields and the table only has 8 fields then why not define it as the
primary key? For a start you may actually get better performance on some
engines as only the index needs to be accessed not the base table.

AutoNumbers, Identity, OID, ROWID and Record Numbers are non-relational
procedural programmer constructs, okay so there are not obscure (but they
should be - coming from the punched tape days before R) and they should be
avoided at all costs. That is not to say that exposed surrogates (I think I
prefer to call these artificial keys) should not be used but these should
come from the real world, Account Numbers, Bar Codes, EANs, UPC, VIN, ISBN
or a carefully designed new one for use by the users.

There are certain fairly rare occasions when one cannot find a natural key
and that is when a designed artificial key should be employed, but not an
exposed surrogate such as AutoNumber or Identity.

If SQLS employed a hashing algorithm like other "grown-up" DBMSs then using
a new surrogate/artificial key, when a natural key existed would add
overhead. Hashing algorithms generate a DBMS internal reference to join
records in related tables (a true surrogate key!). The user and that
includes the Database Designer do not even see these numbers.

Designing and implementing the "correct" design without surrogates (as
defined in your message) will allow your database to be infinitely more
portable.

I am not sure where I saw it first but I think the saying that using a
surrogate/artificial key where a perfectly good natural key exists is like
wearing two watches, one is never sure what time it is. :-)


--
Slainte

Craig Alexander Morrison


"Lynn Trapp" wrote in message
...
Ok, I'm about to step between two opposing views and will probably get
clobbered from both sides but, oh well.

How will that guarantee uniqueness? It will only guarantee that the
Autonumber field is unique.

73863838, Duplicate Data
98597368, Duplicate Data
69409893, Duplicate Data

I agree with you completely here, Craig. It is possible to go on ad
infinitum adding duplicate records, except for the Autonumber field. (gd&r
from my friend Rebecca, promising to bake her a very special loaf of
bread)

If one can define a natural key but insist, for some obscure reason, to
add
an AutoNumber then the natural key should be defined as Unique, Required
and
Not Null.

Actually, though, the reasons for doing so are not quite so obscure.
First,
and probably most importantly, a surrogate key provides an easier way to
create joins between related tables. Trying to build a join on a long
natural key can be pretty unwieldly. Second, most all of us have
encountered
those times when finding a natural key would require including almost, if
not entirely, every field in the table in the primary key. There are some
tables where I have yet to be convinced that it is even possible to find a
completely fool proof natural key -- one that can never be duplicated. In
those situations, also, not even a unique constraint will work. Some other
method of preventing duplication is required -- such as a check that gives
the user the final choice of allowing the duplicate to be committed to the
database or not.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Craig Alexander Morrison" wrote in message
...
you can create an Autonumber field, which will guarantee the
uniqueness
of
each record.

How will that guarantee uniqueness? It will only guarantee that the
Autonumber field is unique.

73863838, Duplicate Data
98597368, Duplicate Data
69409893, Duplicate Data

Only if one cannot define the natural key should one resort to a
surrogate,
and probably best not to be Autonumber if it is to be exposed to the
user.

If one can define a natural key but insist, for some obscure reason, to
add
an AutoNumber then the natural key should be defined as Unique, Required
and
Not Null.

--
Slainte

Craig Alexander Morrison


"Rebecca Riordan" wrote in message
...
Two ways, depending on what you're trying to achieve. You can create
a
unique index, specifying whatever combination of fields is
appropriate,
or
you can create an Autonumber field, which will guarantee the
uniqueness
of
each record.

HTH

--
Rebecca Riordan, MVP

Oh, my apologies. Sloppy thinking on my part. Of course a unique
identifier is not the same thing as a unique record. (Dirk Goldgar, where
are you when I need you? eg)

Now who doesn't get sloppy from time to time? Surely, you are not the only
person to ever do that.

And Lynn, while I agree with your example, I would argue that if you can't
reliably distinguish between two records, then for the purposes of the
system (and only for the purposes of the system), they're the same entity.

What value is there to storing the same entity more than once in a database
system? Doesn't that show a flaw in design somewhere?

(Which for some reason reminds me of a recent remark of my distinguished
father's: "I agree with you completely, but I'm wrong.")

That's similar to the baseball umpire's credo: "I may not be right, but I'm
never wrong."

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

What is the problem with a compound index? If a table's primary key
consists
of 8 fields and the table only has 8 fields then why not define it as the
primary key? For a start you may actually get better performance on some
engines as only the index needs to be accessed not the base table.

If it's only 8 fields then I don't really have a problem with it. It's when
we start talking about needing a compound index of 50 or 60 fields that I
start to worry. Of course, theoretically, you are right. However, theory
doesn't live in the real world -- it has it's own domain.

AutoNumbers, Identity, OID, ROWID and Record Numbers are non-relational
procedural programmer constructs, okay so there are not obscure (but they
should be - coming from the punched tape days before R) and they should be
avoided at all costs. That is not to say that exposed surrogates (I think
I
prefer to call these artificial keys) should not be used but these should
come from the real world, Account Numbers, Bar Codes, EANs, UPC, VIN, ISBN
or a carefully designed new one for use by the users.

This one I agree with almost 100%. I agree that surrogates should come from
the real world, but there's is nothing "other-worldly" g about an
artificial numbering IN COMBINATION WITH other attributes. We use them in
the real world all the time to identify entities -- Joe's Diner #1, Joe's
Diner #2, etc. -- but they should be, as you said carefully designed. That
being said, I am not at all opposed to using a purely artificial number
(AutoNumber, Identity, etc.) as a part of that carefully designed artificial
key. Accounting programs do it all the time when they build charts of
accounts made up of multiple segments of virtually meaningless numbers.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

"Lynn Trapp" wrote in message
...

And Lynn, while I agree with your example, I would argue that if you
can't
reliably distinguish between two records, then for the purposes of the
system (and only for the purposes of the system), they're the same
entity.

What value is there to storing the same entity more than once in a
database
system? Doesn't that show a flaw in design somewhere?


Not necessarily a flaw. To excerpt from Designing, the shipping system
really doesn't care whether you're Lynn, Sr. or Lynn, Jr. For the purposes
of the system, there's only one Lynn Trapp who lives at wherever it is that
you live.

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

Not necessarily a flaw. To excerpt from Designing, the shipping system
really doesn't care whether you're Lynn, Sr. or Lynn, Jr. For the
purposes
of the system, there's only one Lynn Trapp who lives at wherever it is
that
you live.

But in this case your actually dealing with 2 different entities -- Lynn Sr.
and Lynn Jr. If someone enters 2 records for Lynn Sr., who in this case is
really the only Lynn living at a given address, then Lynn Sr. orders 1 item
from the company, it could conceivably turn out that the shipping system
would create a shipping order for 2 of the items to Lynn.

I actually had something like this happen -- although I'm not sure the cause
of it was a duplicate record in the table, but it could have been. I ordered
a new computer and, when the shipment came, they had shipped 2 monitors.
Both of the monitor boxes had packing slips addressed to me. Everything on
the slips was identical, with the probably exception of the monitor serial
numbers, etc. I had ordered an upgraded monitor and I'll bet dollars to
donuts that there was some kind of failure in the data integrity checking in
that company's system.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Rebecca Riordan" wrote in message
...

"Lynn Trapp" wrote in message
...

And Lynn, while I agree with your example, I would argue that if you
can't
reliably distinguish between two records, then for the purposes of the
system (and only for the purposes of the system), they're the same
entity.

What value is there to storing the same entity more than once in a
database
system? Doesn't that show a flaw in design somewhere?


Not necessarily a flaw. To excerpt from Designing, the shipping system
really doesn't care whether you're Lynn, Sr. or Lynn, Jr. For the
purposes
of the system, there's only one Lynn Trapp who lives at wherever it is
that
you live.

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress