How would I do with sql server a Decimal Primary key that would be table identifier.

ie. Table name: Product
PID, PK and I want PID in database in form 1.1 and next product would be 2.1
NAME CHAR

How can I do this

Please help.

Change it to an int and make it an identity

or do it with a trigger manually


http://sqlservercode.blogspot.com/

But I Want it to be a decimal int can't take number lik 1.1 or 2.1
I need to have identity key in decimal form.

You can't do that
What you can do is maintain this yourself with triggers, stored procedures and transactions

http://sqlservercode.blogspot.com/

why can't I do it. Why must the identifier key be a int why can't it be decimal

While it is possible to assign the IDENTITY property to a column defined as a decimal (or numeric) type, you are forced to define the identity increment using a size value of 0, meaning that only integer values of this decimal column will be assigned as identity values.

You are making a big mistake attempting to assign meaning to an identity column beyond the fact that the identity property guarantees unique values automatically assigned by the system when a row is inserted. I think you are attempting to attach some other meaning to this key by assigning fractional values. Perhaps something like a version number or assembly part or something?

Create instead a separate column for this part number/product assembly identifier or whatever it is and don't confuse it with an identity value. Manage it in your code or via a trigger as others have suggested.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I would use it to transfear it to a shopping cart. But problem is that I have two tables (products and table for productpackages) . Both tables have products that should be able to add in shopping cart. But if I put them like I have them now , so in some moment the would have same identity key and then crash

Create a lookup table
with an identity and store all the combinations in 2 other fields

ID prodid packageid
1 1 1
2 2 1
3 1 2
4 2 2
5 3 2
6 1 1

etc etc etc
Then use that identity in your cart

http://sqlservercode.blogspot.com/

how can I do in database that the identity number should always have number 6 or anohter number as prefix

You can use DBCC CHECKIDENT and reseed to 60000000 (600 million)
every insert after that will start with 6 (assuming you don't ship over 100 million products)

Like this
DBCC CHECKIDENT (TableName, RESEED, 60000000 )



http://sqlservercode.blogspot.com/