Guys,
I have a identity flag set on a column. When i am testing the system,
inserting records the identity goes all the way to 60 and so on. I need to
put the table in production system now. However i need to reset the identity
field to start from 1. How can i clear the identity field and make it start
from the begining.
Thanks
MannyUse this
DBCC CheckIdent('TableName')
"Manny Chohan" wrote:
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||I'm not sure why you care about the starting value but you can either
truncate the table or execute DBCC CHECKIDENT with the RESEED option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||Dan,
Just fyi, dbcc checkident defaults to reseed... so,
checkident ('TableName')
is equivilent to
checkident ('TableName', RESEED)
"Dan Guzman" wrote:
> I'm not sure why you care about the starting value but you can either
> truncate the table or execute DBCC CHECKIDENT with the RESEED option.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||Thanks All
"CBretana" wrote:
> Dan,
> Just fyi, dbcc checkident defaults to reseed... so,
> checkident ('TableName')
> is equivilent to
> checkident ('TableName', RESEED)
> "Dan Guzman" wrote:
>|||I am with Dan all the way on this one. Why do you care about the value of
the identity? I admit that I would probably want to reset it myself when
going into production, just because it looks more "tidy." It is however,
always concerning when someone states that they want to know how to do this
because it often means they are using these values in some manner where the
user will care about the value, and identities are pretty bad for this sort
of thing (users HATE gaps, and gaps are just generally part of the identity
experience, since row with the identity property cannot be updated (ever).)
----
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 :)
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||In theory, I agree 100% with you and Dan on this... I do not believe the
value of any surrogate key, much less an Identoty, should be significant to
anyone...
However, in practice, simply out ofa sense of esthetics, I find myself doing
exactly the same thing this gentleman asked about...
In theory, if we really didn't care about the actual values, we wouldn't
always set the seed value for Identity columns = 1, we'd set it to the lowes
t
legal value for the underlying datatype, (TinyInt, SmallInt, Int).
(Either zero, (0), -32,768, or -2,147,483,648, respectively)
But practically, we do care what the value is, because in coding, and
debugging, and manipulating the data in Query Analyzer, we use these values,
and so they do matter.
"Louis Davidson" wrote:
> I am with Dan all the way on this one. Why do you care about the value of
> the identity? I admit that I would probably want to reset it myself when
> going into production, just because it looks more "tidy." It is however,
> always concerning when someone states that they want to know how to do thi
s
> because it often means they are using these values in some manner where th
e
> user will care about the value, and identities are pretty bad for this sor
t
> of thing (users HATE gaps, and gaps are just generally part of the identit
y
> experience, since row with the identity property cannot be updated (ever).
)
> --
> ----
--
> 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 :)
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||That was pretty much what I meant too. It is really a weird thing too.
Sure when we start out programming it is easier to have the value start out
at 1, since it is easier to type while doing initial programming, but by all
logic if we really mean that we don't care about the value then starting
at -minvalue would be better (if we are lucky our values will get that big
anyhow!)
----
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 :)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:7EF6C6ED-EE40-4AA2-8173-8A99226E0CEA@.microsoft.com...
> In theory, I agree 100% with you and Dan on this... I do not believe the
> value of any surrogate key, much less an Identoty, should be significant
> to
> anyone...
> However, in practice, simply out ofa sense of esthetics, I find myself
> doing
> exactly the same thing this gentleman asked about...
> In theory, if we really didn't care about the actual values, we wouldn't
> always set the seed value for Identity columns = 1, we'd set it to the
> lowest
> legal value for the underlying datatype, (TinyInt, SmallInt, Int).
> (Either zero, (0), -32,768, or -2,147,483,648, respectively)
> But practically, we do care what the value is, because in coding, and
> debugging, and manipulating the data in Query Analyzer, we use these
> values,
> and so they do matter.
>
> "Louis Davidson" wrote:
>|||Manny,
you have two options:
1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN A
TABLE WITHOUT ROLLBACK OPTION!)
Pawel
"Manny Chohan" wrote:
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
TRUNCATE does allow a ROLLBACK. However, like other SQL data modification
statements, the transaction will need to be started explicitly unless
IMPLICIT_TRANSACTIONS is on.
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT Col1 FROM MyTable
ROLLBACK
SELECT Col1 FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Pawel Potasinski" <PawePotasiski@.discussions.microsoft.com> wrote in
message news:11BF931F-D7BC-41A3-B0FD-E2F85943B14B@.microsoft.com...
> Manny,
> you have two options:
> 1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
> Pawel
> "Manny Chohan" wrote:
>
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Monday, March 26, 2012
Friday, March 9, 2012
question on inserting a record on sql server with identity column as key
Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HSHongyu Sun (sun@.cae.wisc.edu) writes:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C,
D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the
> "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID"
> and the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is
> violated or missing.
Could you please post the exact error message?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
question on inserting a record on sql server with identity column as key
Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HSThere are two options depending your answer to this question:
Do you want you myTableOnSQLServer table to have the same value of ID from
myTableonAccessLocal.
1. If NO. Then don't specify the ID column in your INSERT INTO statement.
2. If YES. Use SET IDENTITY_INSERT command to allows explicit values to be
inserted into the identity column of a table. Like this:
SET IDENTITY_INSERT myTableOnSQLServer ON
insert into ....
SET IDENTITY_INSERT myTableOnSQLServer OFF
Hope it helps.
"Hongyu Sun" <sun@.cae.wisc.edu> wrote in message
news:dq7fe7$f6b$1@.news.doit.wisc.edu...
> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C,
D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the
"myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID"
and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is
violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
>
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HSThere are two options depending your answer to this question:
Do you want you myTableOnSQLServer table to have the same value of ID from
myTableonAccessLocal.
1. If NO. Then don't specify the ID column in your INSERT INTO statement.
2. If YES. Use SET IDENTITY_INSERT command to allows explicit values to be
inserted into the identity column of a table. Like this:
SET IDENTITY_INSERT myTableOnSQLServer ON
insert into ....
SET IDENTITY_INSERT myTableOnSQLServer OFF
Hope it helps.
"Hongyu Sun" <sun@.cae.wisc.edu> wrote in message
news:dq7fe7$f6b$1@.news.doit.wisc.edu...
> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C,
D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the
"myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID"
and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is
violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
>
Question on Identity columns in 2005.
Howdy all. I used to frequent this forum and hope all has been well with
Paul, Hillary, and any other regulars I may have missed. Anyways, I just set
up Transaction, Immediate Updating replication on two 2005 boxes,
replicating the adventureWorks DB. One table in particular
(Person.AddressType) contains an Identity coulmn, and I did nothing to alter
it in any way. Anyways, once replication was fully configured, I inserted a
row into the table on the Subscriber, and it was assigned Identity value
20009. Questions:
1. It's been a while since I've done anything with Immediate Updating stuff,
but it seems to me that in 2000 I would have had to manually configure the
Identity value on the Subscriber box to use a different range of Identity
values than on the Publisher, right?
2. I looked on the Subscriber table Identity column, expecting the Seed to
be 20009, but it's not, it's 1! How can it be getting the value of 20009 if
the Seed is still set to 1?
TIA, ChrisR
Hi Chris - good to see you're back here!
The automatic identity range management is one of the defaults that has
changed across versions.
The seed and increment is the same behaviour though across versions. This
really refers to the table creation on the publisher. Run DBCC
CHECKIDENT(tablename) at the subscriber to get the new value (or look at the
check constraints on the subscriber's table). I'm guessing that it made
sense that it remains as the original because the new one would at some time
become out of date anyway when a new range is requested, so the easiest
solution was to just take the old table script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. But why is it "ignoring" the value of 1 that I see in
Management Studio?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SpgBYBMHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Hi Chris - good to see you're back here!
> The automatic identity range management is one of the defaults that has
> changed across versions.
> The seed and increment is the same behaviour though across versions. This
> really refers to the table creation on the publisher. Run DBCC
> CHECKIDENT(tablename) at the subscriber to get the new value (or look at
> the check constraints on the subscriber's table). I'm guessing that it
> made sense that it remains as the original because the new one would at
> some time become out of date anyway when a new range is requested, so the
> easiest solution was to just take the old table script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||The idenity value is being reset on initialization using DBCC CHECKIDENT, so
the defined value is now 'meaningless'.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWuRb7BMHHA.4244@.TK2MSFTNGP04.phx.gbl...
> The idenity value is being reset on initialization using DBCC CHECKIDENT,
> so the defined value is now 'meaningless'.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
Paul, Hillary, and any other regulars I may have missed. Anyways, I just set
up Transaction, Immediate Updating replication on two 2005 boxes,
replicating the adventureWorks DB. One table in particular
(Person.AddressType) contains an Identity coulmn, and I did nothing to alter
it in any way. Anyways, once replication was fully configured, I inserted a
row into the table on the Subscriber, and it was assigned Identity value
20009. Questions:
1. It's been a while since I've done anything with Immediate Updating stuff,
but it seems to me that in 2000 I would have had to manually configure the
Identity value on the Subscriber box to use a different range of Identity
values than on the Publisher, right?
2. I looked on the Subscriber table Identity column, expecting the Seed to
be 20009, but it's not, it's 1! How can it be getting the value of 20009 if
the Seed is still set to 1?
TIA, ChrisR
Hi Chris - good to see you're back here!
The automatic identity range management is one of the defaults that has
changed across versions.
The seed and increment is the same behaviour though across versions. This
really refers to the table creation on the publisher. Run DBCC
CHECKIDENT(tablename) at the subscriber to get the new value (or look at the
check constraints on the subscriber's table). I'm guessing that it made
sense that it remains as the original because the new one would at some time
become out of date anyway when a new range is requested, so the easiest
solution was to just take the old table script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. But why is it "ignoring" the value of 1 that I see in
Management Studio?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SpgBYBMHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Hi Chris - good to see you're back here!
> The automatic identity range management is one of the defaults that has
> changed across versions.
> The seed and increment is the same behaviour though across versions. This
> really refers to the table creation on the publisher. Run DBCC
> CHECKIDENT(tablename) at the subscriber to get the new value (or look at
> the check constraints on the subscriber's table). I'm guessing that it
> made sense that it remains as the original because the new one would at
> some time become out of date anyway when a new range is requested, so the
> easiest solution was to just take the old table script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||The idenity value is being reset on initialization using DBCC CHECKIDENT, so
the defined value is now 'meaningless'.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWuRb7BMHHA.4244@.TK2MSFTNGP04.phx.gbl...
> The idenity value is being reset on initialization using DBCC CHECKIDENT,
> so the defined value is now 'meaningless'.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
Question on Idenity Columns
Is there a way to query any system tables, like sysobjects, for User tables
with Identity columns?
We have a SQL2000 server that we want to replicate. One of my tasks is to
identify all tables with Identity columns that are missing the 'not for
replication' clause.
with Identity columns?
We have a SQL2000 server that we want to replicate. One of my tasks is to
identify all tables with Identity columns that are missing the 'not for
replication' clause.
Thanks,
JoeyDJoeyD (joeydba@.yahoo.com) writes:
> Is there a way to query any system tables, like sysobjects, for User
> tables with Identity columns? We have a SQL2000 server that we want to
> replicate. One of my tasks is to identify all tables with Identity
> columns that are missing the 'not for replication' clause.
SELECT object_name(id), name
FROM syscolumns
WHERE columnproperty(id, name, 'IsIdentity') = 1
AND columnproperty(id, name, 'IsIdNotForRepl') = 1
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland - Thank you for your reply.
JoeyD
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns961D3C9067A9Yazorman@.127.0.0.1...
> JoeyD (joeydba@.yahoo.com) writes:
> > Is there a way to query any system tables, like sysobjects, for User
> > tables with Identity columns? We have a SQL2000 server that we want to
> > replicate. One of my tasks is to identify all tables with Identity
> > columns that are missing the 'not for replication' clause.
> SELECT object_name(id), name
> FROM syscolumns
> WHERE columnproperty(id, name, 'IsIdentity') = 1
> AND columnproperty(id, name, 'IsIdNotForRepl') = 1
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
Question on design with Identity columns
Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.
Does anyone see a reason why an identity column would be more or less desireable ? For example
Table Person
PK - PersonID
Table Car
PK - CarID
Table PersonCar
PK (PersonID, CarID)
Or would it be better to make an Identity Column such as PersonCarID so then the table would look like the following:
Table PersonCar
PK - (PersonCarID)
FK - PersonID
FK - CarID
Create Unique Constraint on Person and CarID
Any feedback is appreciatedHave a "quick" read of...
http://forums.asp.net/1015568/ShowPost.aspx
|||
My opinion, which I am not willing to defend to death, mind you, isthat an additional identity column is preferable. I've learned tohate composite keys on tables. I prefer my UPDATE, DELETE, etc.statements to have a single variable in the WHERE condition. Whencommunicating a specific record to another developer, or making a noteto myself, it is a lot easier to be able to refer to a single value asa key. Database purists will certainly scoff, and perhaps evenrise up in outrage. But the additional identity column has servedme well so far.
|||
I have heard pros and cons for having the identity remain Clustered but I think it would best serve me to move it to one of the other columns which is less unique. Or maybe only have the PK as a clustered index and no other indexes on the table since there is only 3 columns total and the optimizer may prefer index scans vs. index seek anyway.|||
So then what will be using the field for?
|||
In that case, you would indeed be using the field in queries, behindthe scenes. Adding a field which would never be used orreferenced of course would make no sense, that's why I questioned you.:-)
|||
The fact that you're asking this question shows that you have a total lack of understanding on relational databases. You need to go to back to the fundamentals. Actually, I'm guessing you never started at the fundamentals and just dived right into SQL Server and started programming. That's OK, a lot of people do that.
If you are being paid to program, you have a professional obligation to do it right instead of making it up as you go along. I'm sure you wouldn't be too happy if all your electrician had a multimeter, wire trimmers, and eletrical tape but didn't know his Volts from his Watts.
Go pick up Date's INTRODUCTION TO DATABASE SYSTEMS. You can get an old edition (it's a text book and doesn't change too much) for $5 or so.|||
In that case, you would indeed be using the field in queries, behind the scenes. Adding a field which would never be used or referenced of course would make no sense, that's why I questioned you. :-)
I agree completely and again that is how I normally do it. But I have seen others add the identity column so I just wondered if there was some underlying reason for doing so. I think not in this case other than they wanted to have a one column key.|||I suspect it is as simple as wanting a one column key 'cause it makes using things like object dictionaries far easier when you've only got to worry about one value. I know I do that.|||
A "complete understanding of databases"? Perhaps you mean CODASYL databases? Or XML databases? Certainly not relational.
In the relational world, we don't do things like add artificial pointers to relations. In fact, that's a fundamental concept of relational databases: no pointers. The whole point of relational databases is to use values on their own to key and relate. Not meaningless pointers.
Your sample schema shows you don't get that, and therefore lack an understanding of that fundamental concept. CarID? What on earth is that? The standard that the rest of the world is VIN. It's right there, physically stamped on every car. In multiple places. It cannot be changed.
Ditto for PersonID. First off, the name "Person" is a very poor naming choice for an individual: companies have Employees and Customers/Clients, schools have Faculty and Students. No organization has generic "Persons." When you model what the actual data is, you find that they already have identifiers there for you. Employee_Num or SSN. Student_Num. Etc. No need to use an artificial pointer.
As I stated earlier, you do not understand these fundamental concepts of relational databases. If you did, you would never need to ask such a question. But what's worse is that you *think* you do.
I do not intend to be insulting. Your question is tantamount to someone asking on forums.carpentry.net: "I normally use a hacksaw when I'm building stuff for my customers ... but what tool do you guys think I should use? A hacksaw or a mitre box? I'm trying to pound in a nail ..."|||Alex makes some fair points, however, I'm sure he'll agree that even pure database design will conceed to the practical natures of a solution, for example de-normalised data. What I'm poining out is that there is *never* a abs. correct answer, the only true answer is, "it depends". However, I do think it would be well worth your time to invest in an introduction to relational database design, at least when you take the decision to use an artifical key you'll know the reasons why ;)
|||So you are saying in Relational Databases meaningless keys such as Identity Columns are bad? VIN For example includes characters thus cannot be an integer or numeric data type.
I would not want VIN as the PK. I typically avoid varchar or char datatypes as primary keys. And yes I typically use identity columns as Primary Keys
In your carID example I would Instead put VIN as unique and would add an Identity column as the PK. Furthermore for anytable that had the a reference to the Car table, It would store a smaller more efficient integer as opposed to varchar. I know that storage isn't much of an issue today as say 10-20 years ago but I still find that a more efficient design.
Plus what if the vin number was incorrect and had to be changed to another unique vin number? This way the change would not have to propogate through any tables that referenced car other than the car table. Same with Employee SSN. While that would be numeric by removing dashes I still prefer a meaningless key.
In your person example you indicated taht What kind of name is person ? Well Employee is a person, Student is a person. You would either have a type table to know which is which but what happens is a person can be both an employee and student and you need to differentiate ? You sure are not going to make two records, so either you have many-to-many table between Person and Person Type or you would have Person and then Employee and Student tables where the PK from the Person is also the PK in the Employee and student table. I would do this if there many attributes that they did not share in common otherwise having the many to many with types would make more sense if they shared all other attributes in common.
I understand that many do not like synthetic keys but I often prefer to keep my PK meaningless when Possible. I do understand the arguments against it but do not always agree especially when using non-integer values as the Primary key.
Does anyone see a reason why an identity column would be more or less desireable ? For example
Table Person
PK - PersonID
Table Car
PK - CarID
Table PersonCar
PK (PersonID, CarID)
Or would it be better to make an Identity Column such as PersonCarID so then the table would look like the following:
Table PersonCar
PK - (PersonCarID)
FK - PersonID
FK - CarID
Create Unique Constraint on Person and CarID
Any feedback is appreciatedHave a "quick" read of...
http://forums.asp.net/1015568/ShowPost.aspx
|||
mru22 wrote:
Does anyone see a reason why an identity column would be more or less desireable ?
My opinion, which I am not willing to defend to death, mind you, isthat an additional identity column is preferable. I've learned tohate composite keys on tables. I prefer my UPDATE, DELETE, etc.statements to have a single variable in the WHERE condition. Whencommunicating a specific record to another developer, or making a noteto myself, it is a lot easier to be able to refer to a single value asa key. Database purists will certainly scoff, and perhaps evenrise up in outrage. But the additional identity column has servedme well so far.
|||
Another typical counter-identity worry is that as they auto-inc then all the new data 'bottlenecks' at the end of the indexes (when your identity is involved in an index). Although that can work for you depending upon your solution.
I have heard pros and cons for having the identity remain Clustered but I think it would best serve me to move it to one of the other columns which is less unique. Or maybe only have the PK as a clustered index and no other indexes on the table since there is only 3 columns total and the optimizer may prefer index scans vs. index seek anyway.|||
I would never be doing any joins on i and it would never been used in any queries.
So then what will be using the field for?
|||
It would just be used to have the PK represent a single column vs. multi column. No other reason, that's why I was contemplating using it in the first place. Typically I have not when I have "many to many" tables.
So instead i would just have the two keys, one from each of the parent tables. With that being said, I notice that the OR Mappers seem to work more easily when you define a single column primary key and if I end up going that route then I may consider the single column pk.
With that being said, I notice that the OR Mappersseem to work more easily when you define a single column primary keyand if I end up going that route then I may consider the single columnpk.
In that case, you would indeed be using the field in queries, behindthe scenes. Adding a field which would never be used orreferenced of course would make no sense, that's why I questioned you.:-)
|||
Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.
Does anyone see a reason why an identity column would be more or less desireable ?
The fact that you're asking this question shows that you have a total lack of understanding on relational databases. You need to go to back to the fundamentals. Actually, I'm guessing you never started at the fundamentals and just dived right into SQL Server and started programming. That's OK, a lot of people do that.
If you are being paid to program, you have a professional obligation to do it right instead of making it up as you go along. I'm sure you wouldn't be too happy if all your electrician had a multimeter, wire trimmers, and eletrical tape but didn't know his Volts from his Watts.
Go pick up Date's INTRODUCTION TO DATABASE SYSTEMS. You can get an old edition (it's a text book and doesn't change too much) for $5 or so.|||
Sorry but I do have a complete understanding of databases. And no I did not just pick SQL server and start programming. Your insults are not only a waste of posting to this board by providing no true value to the discussion but that's ok because there will always be people like you.
As far as this thread why do you go ahead and explain which one you prefer and why or would you rather reply with another insult ?
With that being said, I notice that the OR Mappers seem to work more easily when you define a single column primary key and if I end up going that route then I may consider the single column pk.
In that case, you would indeed be using the field in queries, behind the scenes. Adding a field which would never be used or referenced of course would make no sense, that's why I questioned you. :-)
I agree completely and again that is how I normally do it. But I have seen others add the identity column so I just wondered if there was some underlying reason for doing so. I think not in this case other than they wanted to have a one column key.|||I suspect it is as simple as wanting a one column key 'cause it makes using things like object dictionaries far easier when you've only got to worry about one value. I know I do that.|||
Sorry but I do have a complete understanding of databases. And no I did not just pick SQL server and start programming. Your insults are not only a waste of posting to this board by providing no true value to the discussion but that's ok because there will always be people like you.
As far as this thread why do you go ahead and explain which one you prefer and why or would you rather reply with another insult ?
A "complete understanding of databases"? Perhaps you mean CODASYL databases? Or XML databases? Certainly not relational.
In the relational world, we don't do things like add artificial pointers to relations. In fact, that's a fundamental concept of relational databases: no pointers. The whole point of relational databases is to use values on their own to key and relate. Not meaningless pointers.
Your sample schema shows you don't get that, and therefore lack an understanding of that fundamental concept. CarID? What on earth is that? The standard that the rest of the world is VIN. It's right there, physically stamped on every car. In multiple places. It cannot be changed.
Ditto for PersonID. First off, the name "Person" is a very poor naming choice for an individual: companies have Employees and Customers/Clients, schools have Faculty and Students. No organization has generic "Persons." When you model what the actual data is, you find that they already have identifiers there for you. Employee_Num or SSN. Student_Num. Etc. No need to use an artificial pointer.
As I stated earlier, you do not understand these fundamental concepts of relational databases. If you did, you would never need to ask such a question. But what's worse is that you *think* you do.
I do not intend to be insulting. Your question is tantamount to someone asking on forums.carpentry.net: "I normally use a hacksaw when I'm building stuff for my customers ... but what tool do you guys think I should use? A hacksaw or a mitre box? I'm trying to pound in a nail ..."|||Alex makes some fair points, however, I'm sure he'll agree that even pure database design will conceed to the practical natures of a solution, for example de-normalised data. What I'm poining out is that there is *never* a abs. correct answer, the only true answer is, "it depends". However, I do think it would be well worth your time to invest in an introduction to relational database design, at least when you take the decision to use an artifical key you'll know the reasons why ;)
|||So you are saying in Relational Databases meaningless keys such as Identity Columns are bad? VIN For example includes characters thus cannot be an integer or numeric data type.
I would not want VIN as the PK. I typically avoid varchar or char datatypes as primary keys. And yes I typically use identity columns as Primary Keys
In your carID example I would Instead put VIN as unique and would add an Identity column as the PK. Furthermore for anytable that had the a reference to the Car table, It would store a smaller more efficient integer as opposed to varchar. I know that storage isn't much of an issue today as say 10-20 years ago but I still find that a more efficient design.
Plus what if the vin number was incorrect and had to be changed to another unique vin number? This way the change would not have to propogate through any tables that referenced car other than the car table. Same with Employee SSN. While that would be numeric by removing dashes I still prefer a meaningless key.
In your person example you indicated taht What kind of name is person ? Well Employee is a person, Student is a person. You would either have a type table to know which is which but what happens is a person can be both an employee and student and you need to differentiate ? You sure are not going to make two records, so either you have many-to-many table between Person and Person Type or you would have Person and then Employee and Student tables where the PK from the Person is also the PK in the Employee and student table. I would do this if there many attributes that they did not share in common otherwise having the many to many with types would make more sense if they shared all other attributes in common.
I understand that many do not like synthetic keys but I often prefer to keep my PK meaningless when Possible. I do understand the arguments against it but do not always agree especially when using non-integer values as the Primary key.
Subscribe to:
Posts (Atom)