how to ensure two tables should never have same ids??

[mrpro]

Hi gurus
I have two tables

users(userid int,name varchar(50))
userclass(classid int,name varchar(50))

I want to make sure these two tables should never have same id's


Why i need this is i want to find whether a given id is actually a classid or just userid.

If it is a classid then i should retrieve all userid's in that classid(this is stored in seperate table userclassmembers)


i think one way of doing this is

when we create newid(by using select max(userid))check this against other table whether this id exists or not.if not exists then create id happily.


Any ideas on how i could go about this would be greatly appreciated.

 

[donutman]

Make one odd the other even.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Having two tables isn't really necessary to accomplish that, but you may have other reasons.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

I agree with donutman. But to further explain:

Create each id as an IDENTITY. And then 'seed' them appropriately.

UserID INTEGER IDENTITY(1,2)

ClassID INTEGER IDENTITY(2,2)

Refer to the BOL for more information.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Posting advice: FAQ481-4875

 

[mrpro]

thanks guys this should solve never thought about it..

but my table doesn't use identity(can't change that)on the fields so i need to go for


begin trans
select max(userid) and increment odd or even
commit trans

do i need to use rollback or not..
i hope the syntax i have given is correct..

 

[donutman]

The rollback is up to you. You certainly don't need it for just the line of code you wrote. Buy why can't you make UserID an identity column...you are doing the same thing manually.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

Why can't you change it?

ALTER TABLE table_name
ALTER COLUMN UserID INT IDENTITY(1,2)

-SQLBill

Posting advice: FAQ481-4875

 

[bassguy]

Use uniqueIdentifier
then you will never have the problem

 

[donutman]

Why i need this is i want to find whether a given id is actually a classid or just userid.

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrpro]

that's the point..i have not given permission to change the table so no other option..

do i need to use rollback if i use insert or update statements in that stored procedure..

another question is it alwasys advisable to use transactions if we are creating id's using select max()..

any drawbacks using the transactions in my stored procedure
because i am creating id's in many stored procedures

thanks

 

[donutman]

The answer to that question depends upon the situation. Transactions should be used when it's possible that the system will crash in the middle of performing a sequence of db changes that would result in a portion of the transaction having been completed. AND the implication of which is that either the resulting data will be incorrect or the database integrity will be compromised. Just because it's possible that the system will crash in the middle a "transaction" isn't sufficient reason to use transactional processing.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrpro]

thank you for your explonation

but i think we must always use transaction when you are trying to create newid using select max() statement as there is every chance that by the time you get your newid in the stored procedure another user might have created one more id.

so without using transaction we can't ensure database integrity

any one disagree with this point..

thanks

 

[donutman]

That's an interesting thread on it's own. Not everyone is likely to be reading this thread, so may I suggest that you create one regarding that issue. I think a reasonable title would be, Creating the next PK in multiuser environment. I think it might be interesting to learn other programmers techniques for that when not using an identity column. If you do create a new thread, add a reference to it from this thread.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrpro]

thanks donutman

i will create new thread on this topic as i think it will be useful for many techies..