June 30, 2004 at 1:24 pm
Hello,
I've inherited a ASP/SQL solution and I need to 'fix' it.
A Table has been setup with no Primary Key field. The Table contains approx 140 records.
What I'd like to do is
1) establish ID as a primary key field (a number from 1 to 1000)
2) loop through the existing records and insert a unique value into the ID field
I think I can handle future entries into the DB. I'm just not sure how to do the above.
Can anyone help?
JD
July 1, 2004 at 12:32 am
Hi JD
The correct way to setup a primary key / ID field is to create a new column with an INT datatype, and turn on IDENTITY for the column. When you save the change, the table will be populated with unique ID's, and every furture added row will also get a unique id.
Read up about IDENTITY in sql books online.
The SQL you would need would be something like:
ALTER TABLE XCOUNCIL ADD xyzID int NOT NULL IDENTITY(1,1) CONSTRAINT xyzPK PRIMARY KEY
Julian Kuiters
juliankuiters.id.au
July 1, 2004 at 3:33 am
The better thing to do would be to find existing field(s) whose combination will define the entity that table is tracking. Then make that combination or single field a primary key. You kill 2 birds with one stone, 1) you separate attributes from entity definition, so it is easy to know what the table is for
2) you enforce integrity so you never have duplicates even if app end screws up
If you wish to use PK-FK, make a surrogate key, ID that is, and use that, more accurately should be called SK-FK relationship. This is the best of both worlds. The ID world (republicans) and the Natural Key world (liberals). He he.
July 1, 2004 at 8:42 am
Wow, thanks Julian. That was way easier than writing code.
I was trying to create the ID field yesterday, but kept getting an error message. I was using a numeric datatype. And was trying to make it a Primary Key field instead of turning on IDENTITY.
cool!
July 1, 2004 at 8:08 pm
Glad to hear it worked JD.
IDENTITY columns are best set as INT datatypes, a simple data type and theres plenty of room to grow.
In almost all tables you will need an ID field, and make that an IDENTITY, and primary key. Then you can create relationships between your tables.
These things don't matter so much with a tiny database (less than 50MB) but when you start having to look after larger (100MB - 1 gb+ ) databases, these simple things make life a whole lot easier.
Julian Kuiters
juliankuiters.id.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply