Autonumber

[Michael]

What is the equivelant to Autonumber coming from an Access World to
SQL?
TIA

Michael

[Greg D. Moore \(Strider\)]

"Michael" <Mi***@yahoo.com> wrote in message
news:9h********************************@4ax.com...

What is the equivelant to Autonumber coming from an Access World to
SQL?

IDENTITY


TIA

Michael

[David Portas]

> What is the equivelant to Autonumber coming from an Access World to

SQL?
A proper primary key.

http://www.sqlteam.com/item.asp?ItemID=2599

--
David Portas
------------
Please reply only to the newsgroup
--

"Michael" <Mi***@yahoo.com> wrote in message
news:9h********************************@4ax.com... What is the equivelant to Autonumber coming from an Access World to
SQL?
TIA

Michael

[Michael]

On Sun, 30 Nov 2003 23:56:37 GMT, "Greg D. Moore \(Strider\)"
<mo*****@greenms.com> wrote:

R U referring to uniqueidentity

"Michael" <Mi***@yahoo.com> wrote in message
news:9h********************************@4ax.com.. .

What is the equivelant to Autonumber coming from an Access World to
SQL?

IDENTITY

TIA

Michael

[Trevor Best]

On Mon, 01 Dec 2003 03:46:21 GMT in comp.databases.ms-sqlserver,
Michael <Mi***@yahoo.com> wrote:

On Sun, 30 Nov 2003 23:56:37 GMT, "Greg D. Moore \(Strider\)"
<mo*****@greenms.com> wrote:

R U referring to uniqueidentity

No, that's a GUID, make it an "Int" data type and set the "Identity"
property to "yes"

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

On Mon, 1 Dec 2003 00:08:13 -0000 in comp.databases.ms-sqlserver,
"David Portas" <RE****************************@acm.org> wrote:

What is the equivelant to Autonumber coming from an Access World to
SQL?
A proper primary key.

http://www.sqlteam.com/item.asp?ItemID=2599

hmmm, I use identity columns, their meaningless is nothing to me, I
don't let users see them or touch them, they mean an awful lot to the
computer though :-)

From that page:using only the ID and BossID columns, identify each person's boss. Can't do it, huh?

This assumes you display raw data to the user, in reality you join
tables and make menaingful presentation data to show the user.

In the second table, the candidate is easy, the URL will be unique
although imagine you've exported to another system, then import data
back from that, one or more of your meaningful PKs was misspelled and
corrected in the meantime, there would not be a match when the data
comes back in.

--
A)bort, R)etry, I)nfluence with large hammer.

[David Portas]

My point was not that the OP should necessarily avoid IDENTITY altogether
but that he shouldn't assume he can just port an Access database to SQL by
changing an Autonumber column to IDENTITY. In Access you can get away
without primary keys - not always so in SQL.

It may be reasonable to use IDENTITY as a *surrogate* key but if you don't
have a natural key as well then you can't define the entity for the table
and it may contain redundant data. Consider:

CREATE TABLE foo (id INTEGER IDENTITY PRIMARY KEY /* ? */, a INTEGER NOT
NULL, b INTEGER NOT NULL, c INTEGER NOT NULL)

If (a,b,c) are your attributes then why would they not be unique? What's the
point of storing the same information multiple times? Clearly Id isn't an
attribute of you entity so ([1],x,y,z) and ([2],x,y,z) are redundant tuples.
This is the best way to fill up your table with garbage and make life very
difficult when it comes to joins and other queries.

--
David Portas
------------
Please reply only to the newsgroup
--

[Trevor Best]

On Tue, 2 Dec 2003 10:09:17 -0000 in comp.databases.ms-sqlserver,
"David Portas" <RE****************************@acm.org> wrote:

My point was not that the OP should necessarily avoid IDENTITY altogether
but that he shouldn't assume he can just port an Access database to SQL by
changing an Autonumber column to IDENTITY. In Access you can get away
without primary keys - not always so in SQL.

It may be reasonable to use IDENTITY as a *surrogate* key but if you don't
have a natural key as well then you can't define the entity for the table
and it may contain redundant data. Consider:

CREATE TABLE foo (id INTEGER IDENTITY PRIMARY KEY /* ? */, a INTEGER NOT
NULL, b INTEGER NOT NULL, c INTEGER NOT NULL)

If (a,b,c) are your attributes then why would they not be unique? What's the
point of storing the same information multiple times? Clearly Id isn't an
attribute of you entity so ([1],x,y,z) and ([2],x,y,z) are redundant tuples.
This is the best way to fill up your table with garbage and make life very
difficult when it comes to joins and other queries.

There's nothing to stop you putting a unique index on the other keys.
--
A)bort, R)etry, I)nfluence with large hammer.

[David Portas]

> There's nothing to stop you putting a unique index on the other keys.

Yes, as I said:

It may be reasonable to use IDENTITY as a *surrogate* key but if you don't
have a natural key as well then you can't define the entity for the table

Unfortunately this is too often forgotten as posts to this group frequently
demonstrate. See also Northwind for MS's bad examples.

--
David Portas
------------
Please reply only to the newsgroup
--