| Author |
Topic |
|
Dorffius
Starting Member
36 Posts |
 Posted - 2002-03-04 : 11:06:55
|
| I just added a new column rowNum to my table. I need it to always be the row number so that I can use it as a unique key for updating. How would I accomplish this. I have no access to Enterprise Manager. |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-04 : 17:59:26
|
quote:
I just added a new column rowNum to my table. I need it to always be the row number so that I can use it as a unique key for updating. How would I accomplish this. I have no access to Enterprise Manager.
What method did you use add the column? Access? Did you get the DBA to do it? Did you do it using VB?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-03-04 : 18:09:15
|
| If you don't have access to EM but you do to Query Analyzer this can work:ALTER TABLE FOO DROP COLUMN rowNumGOALTER TABLE FOO ADD rowNum NUMERIC(12) IDENTITY (1,1)This drops the new column you added and puts it back but as an identity with a starting value of 1 and an incrementing value of 1. I think it should work. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-04 : 18:35:58
|
| You may be able to do what James has suggested via other methods if you don't have Query Analyser - However, you need to be very careful about this, do you really want:row numberORunique identityYou can't have both (unless you never have deletes from your table).--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-03-04 : 18:46:08
|
| So why not add the CONSTRAINT rowID_PK PRIMARY KEY to the column when altering the table ("forgot" to do that in the first place.) That gives you a row number and a primary key does it not?ALTER TABLE FOO DROP COLUMN rowIDGOALTER TABLE FOO ADD rowID NUMERIC(12) IDENTITY(1,1) CONSTRAINT rowID_PK PRIMARY KEYGO |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-04 : 18:57:36
|
| no - not if someone deletes a record in the table. Unless you go and re-number all the latter rows, the id will not be the same as the row number - even if it is a prmary key....try thiscreate table A (value nvarchar(50))ALTER TABLE A ADD rowID NUMERIC(12) IDENTITY(1,1) CONSTRAINT rowID_PK PRIMARY KEYGOinsert into A (value) values ('a')insert into A (value) values ('b')insert into A (value) values ('c')delete from A where value = 'a'select rowID from A where value = 'c'the value you get returned is 3 - the unique id - but as there are now only two rows in the table, the row number is 2. I suspect that Dorffius wants a unique key only - and doesn't care about the row number, but I'm sure he'll let us know....otherwise he could always check out [url]http://www.sqlteam.com/item.asp?ItemID=765[/url] (thanks general!)Edited by - rrb on 03/04/2002 20:46:14 |
|
|
|
Dorffius
Starting Member
36 Posts |
Posted - 2002-03-05 : 08:34:07
|
| Thanks a lot. The records that I am working with will never be deleted so it's not a problem at all. The methods worked great. Thanks again. |
|
|
|
JamesT
Yak Posting Veteran
97 Posts |
Posted - 2002-03-05 : 10:30:57
|
| That is correct rrb. I had not thought of the delete and renumbering issue. I would think that rows numbered for the sake of ordering them is not really that important (of course it depends on the application). Don't indexes address that issue anyway? Besides, if it is important as to when a row was inserted why not use some datetime stamp? As Don King, that famous and talented word-smith would say: Unique-icity is contrary to the profoundity of order insertology!Thanks for clarifying this issue for me. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-05 : 17:04:40
|
Nonetheless - your points are well made. Trying to keep a "row" number field is - as you point out - by definition an oxymoron. Row numbers (if they're ever needed) should really only ever be calculated at the output stage - and not stored. Cheers --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
New column needs row number....