Genarating id for a table

[dotneto]

Hi, i have a problem

I have a column in a table (SQL Server 2005) that identifies that table , sometimes it's provided by the user of the system, but in some scenarios, it should be auto generated.
I'm not sure what to do, i can make a design change but it would be better if there is a way to generate a value for that column programatically without conflicting with other values in the table.

[kenobewan]

Please don't make this ID the primary key of the table or you are likely to run into many problems, but do have a primary id key. If the user inputs a userid then you will need to query to ensure that this id hasn't already been used. You could select the max userid and add 1 if this is a case where the id is not user defined. I would be inclined to design a table with 3 ids - primary, user selected & allocated. It will make your queries longer but may save you pain. Good luck.

[dotneto]

Please don't make this ID the primary key of the table or you are likely to run into many problems, but do have a primary id key. If the user inputs a userid then you will need to query to ensure that this id hasn't already been used. You could select the max userid and add 1 if this is a case where the id is not user defined. I would be inclined to design a table with 3 ids - primary, user selected & allocated. It will make your queries longer but may save you pain. Good luck.

Thanks, the idea of having diferent columns as you say is the right way, now i think is what i should do. any way, i was tempted to use that column as PK, because i realized of the design error to late, but i think i was just been lazy.

Thanks