Interesting SQL Dilemma -- Need Help

21 Comments

• INSERT INTO TestIds VALUES (newid()) ?
  

• *smack*, *mumble* read before reply *mumble*
  

• INSERT INTO [TestIds] DEFAULT VALUES
  
  GO
  
  

• Just wondering the purpose of the table...
  
  
  
  The Jeff
  

• Thanks Darshan -- that works great for MS SQL.
  
  
  
  Now, is there a solution for Access also?
  
  
  
  And Jeff -- no real reason other than someone told me that my mapper didn't support it. :)
  

• SET IDENTITY_INSERT TestIds ON
  
  INSERT INTO TestIds (TestId) VALUES (1)
  
  SET IDENTITY_INSERT TestIds OFF
  
  
  
  Should do the trick.
  

• Sorry Scott, but I actually want the identity inserted if that wasn't clear -- I don't want to turn it off and insert an explicit number.
  

• CREATE PROC usp_createID AS
  
  BEGIN
  
  INSERT INTO testids DEFAULT VALUES
  
  SELECT @@IDENTITY
  
  END
  
  GO
  
  
  
  EXEC usp_createID
  

• OK thanks Jason. What if its an Access table?
  

• Paul - for Access, chances are they're using an "AutoNumber" field for a primary key...this should work
  
  
  
  INSERT INTO TestIDs (TestId)
  
  SELECT MAX(TestID) + 1 AS IdValue FROM TestIDs
  
  
  
  Even though it's an AutoNumber it will still let you specify a value to insert...so you could always insert a value that's one higher than the greatest using Max()...not sure if that's ANSI compliant or not though.
  

• Although SELECT @@IDENTITY does work for SQL Server, you should really use:
  
  
  
  SELECT SCOPE_IDENTITY()
  
  
  
  because @@IDENTITY does not work properly if you have certain types of triggers on your table.
  

• The problem is that Access isn't really a database. That's why it can't do a lot of things you take for granted with real database engines.
  

• 
  
  FWIW, as the guy who reported this issue, it's possible for an entity to only play 1:m relationship roles with other entities. In this case the entity would consist of the key and one or more collections.
  
  
  
  The case I reported was for type entity. A customer has a m:1 to it's type and it's type has a 1:m to all customers of that type. In this case the customer type played no other functional roles so the entity consisted of the key CustomerType and the collection Customers.
  
  
  
  jMM
  

• So John, since you've came public :)
  
  I know in your case you seemed to indicate that the read-only option was sufficient, but I'm thinking I should cover all scenarios. Obviously it will work if the single field is not an identity, but I don't want to make it doable in MS SQL for this bizarre case, while still leaving it unsupported for Access. Yes, I still have no solution for Access, since the support doc didn't seem to apply that Jason posted. So right now I'm not going to try to resolve this scenario, although I'll change my mind if I can ever get the Access case to work -- what do you think ?
  

• "And Jeff -- no real reason other than someone told me that my mapper didn't support it. :)"
  
  heh, sounds very familiar. :) These tables are not useful btw, in any scenario, as it is just a number, there is no data identified by the number.
  
  
  
  "A customer has a m:1 to it's type and it's type has a 1:m to all customers of that type. In this case the customer type played no other functional roles so the entity consisted of the key CustomerType and the collection Customers. "
  
  I wonder why you add the type at all. Perhaps a description field is appropriate, so you can specify which type a customer is :) (which will automatically solve your problem). Otherwise setting the type is problematic as the specification of the type is based on a number, but what that number means is vague.
  

• Paul - what about the thing I posted for Access? It won't work?
  

• 
  
  In my senario the key was not an identity but rather a user defined value. Further there is no CustomerType table. The customer type entities are derived from the unique values of customer type in the customer table.
  
  
  
  Yes of course adding a functional description role to customer type resolves the issue by forcing the creation of the customer type table with at least two columns. But I think my senario is realistic and need it to be supported.
  
  
  
  It's possible that someone would want to control the possible values of customer type by forcing customer type into an independent table consisting of a single key column.
  
  
  
  While it's also possible for the key to be a surrogate key, given the difficulty in supporting this variation of the senario and it's unlikeliness, plus the fact that I would never need it :), I see no reason to support it. Whenever possible, I preserve any existing uniqueness constraints when introducing a surrogate key, which would introduce a functional role and resolve the issue.
  
  
  
  jMM
  

• Is it just me or is anyone else severely confused by what John Miller is doing here and what Paul is saying he was asked to support? What point is there in having a key value if it doesn't point to anything?
  

• Everyone: I can see the value of the single field that is user defined, and it looks like that's all John was really after anyhow, although I too can't really claim to fathom all of his statements. :) I wasn't trying to say someone (John in this case) had asked for me to support this specific case -- I was just saying someone had asked for something that was similar enough to make me realize I was not able to handle this case either. Like most of us, I then became too curious to forget it. :) I've actually seen people have a table with identities that they used to get a unique key for other tables without identities, kind of like sequences in Oracle, although I think those tables always had a second "junk" column too -- now I know why.
  
  
  
  Jason M.: There are actually 2 different Jasons posting things, so sorry that I just referred to Jason earlier. Your solution is certainly "doable" if that's the best Access can offer, although I was really hoping for something better I suppose. Afterall, even Access supports @@Identity, although that's not really going to work either in a situation with multiple inserts at the same time. So maybe I will use it, but I think I'm just leaning to saying why bother with this one.
  

• 
  
  To clarify for those having trouble fathoming my earlier posts <g,d&r>
  
  
  
  Given these requirements:
  
  
  
  Every Customer is identified by one distinct Id. Each Customer is assigned some Customer Type. Each Customer is assigned at most one Customer Type.
  
  
  
  Every Customer Type is identified by one distinct Code.
  
  
  
  Examples:
  
  Customer Acme is assigned Customer Type RENT.
  
  Customer Bender is assigned Customer Type SALE.
  
  Customer Carter is assigned Customer Type SALE.
  
  Customer Demble is assigned Customer Type RENT.
  
  
  
  These requirements can be implemented in a Customer table with a CustomerId column and a CustomerTypeCode column where Customer Id is unique.
  
  
  
  A business object representation of these requirements would consist of a Customer class with a Id property, and a CustomerType property which is a reference to a CustomerType object.
  
  
  
  The CustomerType class consists of a Code property and a collection of Customers.
  
  
  
  You could argue that the Customer class needs a CustomerTypeCode property, but I would argue that it's the persistence engine responsibility to populate foreign keys. But thats another thread. <s>
  
  
  
  There is no need for a ClientType table to meet these requirements. Also, the ClientType class has a structure that WORM currently cannot handle. A class with the key as the only property.
  
  
  
  jMM
  
  
  
  

• select max(TestId)+1 into tempTable from TestIds
  
  
  
  insert into TestIds (TestId) select expr1000 from tempTable