January 8, 2013 at 8:59 am
Hi i want to create a unique id column in a table which is identified using data from other columns. The unique id needs to be based on DOB, source of specimen and organism where these are all unique in each row then an id is generated. Any ideas?????
January 8, 2013 at 9:19 am
Why not just have those three columns be unique? A unique constraint can contain multiple columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 9:23 am
I need to assign a unique id as we have an application which handles the data and this is needed to de-duplicate records when running reports another reason why i need to assign an id is there are two stages to our import of data and if there is no unique id then data does not get appended to the master table.
January 8, 2013 at 9:27 am
It actually just needs to an id column not unique in each row but unique in the combination of dob, source of specimen and organism. As we can get multiple rows which will have the same id due to the way the database has been constructed.
January 8, 2013 at 9:30 am
Based on your description, that's not really something a relational database does.
What you need is a separate table of unique combinations of DOB, Source, and Organism. Then assign an ID in that table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 9:42 am
Thanks didn't think of it like that having a separate table could work. Will have a go and let you know how i get on.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply