Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    39

    Unanswered: Auto incrementing Id

    Alright so i am trying to create a table of Inventory items in my database and i want it to look something like this:

    http://img204.imageshack.us/img204/9...ntoryitems.png

    i want the ID to autoincrement with each new row inserted BUT based on the category of the item.. SO for instance if the new row being inserted is in the 'glass' category, then the ID should be =(ID of the last inserted row in the glass category+1)...

    Dont pay too much attention to my explanation.. The table is self explanatory... Thanks in advance!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can separate auto-incrementing for separate categories easily with myisam tables in mysql

    but in microsoft sql server, you have to write a user-defined function or something, the IDENTITY feature is one auto-incrementing sequence overall for the table

    and in fact that's probably what you should do anyway -- embedding intelligence into a key is never a good idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    Smile create a trigegr on the table

    Here is the solution....If you have pre-defined starting values for the products then initialize values in the trigger, I am sure you can do it else implement the exact trigger.

    create trigger <triggername> on <tablename>
    after insert, update
    as
    declare @item char(20);
    declare @category char(20);

    select @item= i.item, @category = i.category from inserted i;

    begin
    declare @a int;
    select @a = max(id) from <tablename> where category = @category;

    begin
    update <tablename> set id = @a + 1
    where category = @category and item = @item; (optional)
    end
    end



    Note :- i guess combination of category and item is unique jusdging by ur example. if not, then remove "item = @item" where i hv mentioned optional.
    If the starting values your product id 20001 then one time job is to insert 20000 instead of 20001.

    Thank you.
    Have a nice day ahead

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It is always a bad design decision to incorporate business logic in a PK.

    Define a true IDENTITY Id column that will give each record its unique number and next to that define a NVARCHAR column to hold whatever business key your main user demands.

    Just imagine that today drinking glasses are categorised under "glasses" and from next week on under "tableware". What will be the implication of that on your system? How about all those Foreign Keys still using those old '1000...' numbers? That will be your future when you allow the business to define your PK. And please do not use ON UPDATE CASCADE as an answer to that. You don't want your system to start changing things in n tables all over your database only by changing 1 value in TableA. Well at least I don't.

    Defend your database against your business and your business against themselves. You will have to deal with the consequences of such design decisions later, don't make your job harder than it should be. You must be able to help your business in a quick way if they change the way they categorise stuff or when they want to refine their categories.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2011
    Posts
    39
    Thanks for the help guys

    Hey Sunny_007, yes the combination category+item name is unique...i understand most of what you did...I'll look more into triggers

    Wim, i completely understand what you are saying. I think I'll just do a regular autoincrement for the entire table.
    Last edited by caa5042; 09-08-11 at 06:24.

  6. #6
    Join Date
    Sep 2011
    Posts
    39
    But i may have to apply a similar logic to another table. Say you have a "Customer Transactions" Table (to store all transactions of all customers) .Well each customer will have multiple transaction records in the table. And you want to have a key column in the transactions table that autoincrements for each transaction record added depending on which customer the transaction is being entered for...hope i dont sound confusing...I'll be back in a moment with a table..

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by caa5042 View Post
    And you want to have a key column in the transactions table that autoincrements for each transaction record added depending on which customer the transaction is being entered for...
    why? why do you want that?

    that's where the problem is, you wanting that

    you're asking for a ~world~ of hurt...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your explanation is clear to me. It is basically the same question as the one that started this thread. So my answer is basically the same.

    You will most likely have a Customer table. As an improvement on sunny_007's solution, you could add a column to that table that will store the highest used sequential number used for that customer. So instead of looking for the highest used number for that customer in your (most likely huge) CustomerTransactions table, you only have to search in (and update) the much smaller Customer table.

    This same logic could also be applied towards your original question.

    You should use a MUTEX when updating and returning the next sequential number. I have never done this before. I came up with this optimistic locking solution (it could suffer from starvation though) for your first question:
    Code:
    DROP TABLE CoType
    
    CREATE TABLE CoType(
    	Id		BIGINT	NOT NULL,
    	Code		VARCHAR(20)	NOT NULL,
    	MaxNumberAssigned BIGINT	NOT NULL
    )
    
    INSERT INTO CoType (Id, Code, MaxNumberAssigned) VALUES
    (1, 'Glass', 0),
    (2, 'Tableware', 100)
    
    if exists (select 1 from  sysobjects where id = object_id('dbo.P_GetMeANewNumberPlz') and type = 'P')
    	DROP PROCEDURE dbo.P_GetMeANewNumberPlz
    go
    
    CREATE PROCEDURE dbo.P_GetMeANewNumberPlz (
    	@TypeId		BIGINT,		-- type
    	@DaNewNumber	BIGINT OUTPUT	-- Next new Id 
    	)
    AS
    BEGIN
    	SET NOCOUNT ON
    
    	if NOT EXISTS (SELECT 1 FROM dbo.CoType WHERE id = @TypeId)
    	BEGIN
    		RAISERROR ('@TypeId has no valid value: %I64d',	16, 1, @TypeId)
    		RETURN
    	END
    
    	DECLARE @MaxNumberAssigned BIGINT
    	DECLARE @Rowcount BIGINT
    
    	SELECT @DaNewNumber = MaxNumberAssigned + 1 FROM dbo.CoType WHERE id = @TypeId
    	SET @Rowcount = 0
    
    	WHILE @Rowcount = 0
    	BEGIN
    		UPDATE dbo.CoType
    		SET MaxNumberAssigned = @DaNewNumber
    		WHERE MaxNumberAssigned = @DaNewNumber - 1
    		
    		SET @Rowcount = @@Rowcount
    	END
    
    END
    GO
    
    DECLARE @ANewNumber Bigint
    EXECUTE dbo.P_GetMeANewNumberPlz @TypeId = 1, @DaNewNumber = @ANewNumber OUTPUT
    PRINT @ANewNumber
    Are there better solutions for this?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •