Results 1 to 7 of 7

Thread: How to make sure I insert unique values in a column when they are not zeroes?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    How to make sure I insert unique values in a column when they are not zeroes?

    I am using vb6 with sql 2000. I have a column 'r_num'. I have to make sure that only distinct values are allowed in the column except for zeroes.

    There will be 3 kinds of insertions.
    • User entering zeroes,
    • User entering numbers (which will be distinct for sure) and
    • Me generating using maximum value and inserting it.


    But there will be multiple users at the same time. Therefore there is chance that duplicates will be coming while generating the values and inserting it. How can I avoid that situation? Thank you

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to make sure I insert unique values in a column when they are not zeroes?

    Several ideas come to mind...

    Create another table that has just one column - an identity column - and have that be used to generate the unique-non-zero values you need. Then use that value on INSERT into your actual table.

    Another idea - although I dislike triggers - is to maybe use a trigger...

    Personally - I do something like this all over our financial applications. But I have the benefit of using stored procedures - so I can package all this logic into the CREATE SPROC.

    Code:
    	RedoB:
    		Set @DataValue=(select DataValue from Control_T where FiscalYr=@FiscalYr and Item='LAST BATCH')
    		Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
    	
    		Update Control_T
    		Set DataValue=@NewDataValue,Tdate=@Tdate
    		Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
    	
    		If @@RowCount<>1 Goto RedoB
    	
    		Set @BatchEntry=Cast(@NewDataValue as int)
    Putting that DataValue=@DataValue part on the UPDATE WHERE clause allows for concurrency - so this is multi-user safe (and has about 15 years of user interaction to prove it!).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to make sure I insert unique values in a column when they are not zeroes?

    I'm not 100% clear on the requirement. Are you asking how you can generate unique numbers in a multiuser set up? If so then you should be fine as long as you make the generation part if the insert statement so they're executed as a single atom. E.g.
    Code:
    Insert Into BlahTable (ID, Description)
    Select Max(ID) + 1, 'Some Descriptive Text'
    From BlahTable
    (It was actually Szlamany who suggested this to to me years ago and I've lived by it ever since ) Because it's a single statement it get's exected as a single unit so no other inserts can intervleave. If you can't phrase it as a single statement for some reason you can wrap it in a transaction to achieve the same result.

    Or are you asking how you ensure users don't enter the same number in there more than once. If that's the case you'd be best implementing a Constraint on the table. Google will give you plenty of info on how to do that.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    Re: How to make sure I insert unique values in a column when they are not zeroes?

    szlamany, Will that identity column allow me to let users put their own number in that column? Because they will. But it will be unique. FunkyDexter,I cannot add constraint because there will be zeroes (only zeroes will repeat) in many rows. Otherwise it will should be unique numbers. That is like these : (0,0,1,3,5,0,8,10...)
    So thEse 3 conditions happening will be : taking maximum when user don't know the number, user putting unique numbers, and putting zeroes when user does not want a value. My concern is when taking maximum, multiple users may be saving the same number. That should not happen. THANK YOU

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to make sure I insert unique values in a column when they are not zeroes?

    Are you using STORED PROCEDURES for any of this?

    But - regardless - you can INSERT you OWN VALUES into an IDENTITY column by "turning" the identity "aspect" off for the connection requiring it.

    If the column in that "control" table is marked as a primary key - then by default it will not allow duplicates - so that would take care of that requirement.

    But really - a stored procedure would give you much, much more control over all this.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    Re: How to make sure I insert unique values in a column when they are not zeroes?

    There is a problem with 'when to turn off the identity aspect' thing. I do not know if the user in going to put their own value or accepting the maximum value which I generated for them. Or there would be zeroes.
    I am not using stored procedure currently. I am using recordset.update method. There will be 20+ parameters to pass to stored procedure.

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to make sure I insert unique values in a column when they are not zeroes?

    20 parameters is not a lot for a sproc - I have sprocs with that all the time.

    Using a SPROC gives you ultimate-server-side control over this - where you can write IF/blocks and such to test for conditions and deal with the varying INSERT scenarios you are going to be throwing at it.

    In a SPROC this would be written in 2 minutes...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

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



Click Here to Expand Forum to Full Width