Paul Wilson's .NET Blog

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.

Here's a way to do it -- classic ASP but the main idea is there.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q221/9/31.ASP&NoWebContent=1

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

Actually, IMHO, it is the SQL standard committee create this whole mass. The purpose of the SQL standardization is originate on the hope that there is a 'standard' way of the "Structure Query Language" which can be used across all kind of the backend.

Granted that using a AutoIncrement number/ timestamp is a bad practice of the "good" database design -- but everyone use it. It bother me that when the SQL committee keep on ignore of the actual usage of the database and add their "value" into the standardization process. When they leave out this section on the standard SQL lanuage --- guess what, all database provider do just their own stuff. All different identity method have on MSSQL, ACCESS, ORACLE, MYSQL, FoxPro, DB2.. all have different meaning and functionality --- the tools writer just get caught in between.

As to why identity column is bad database design -- just don't scale to well, image that for a big database table, your costomer just constantly add 2 records then delete 2 records. (Guss what, the two deleted records identity number can't be reclaimed becuause there is no guarantee if it is used or not when on multiuser secnario.)

The similiar problem go to timestamp, it just doen't scall well. When you have multiple records open on the multiple layer of transaction (if it is supported), does the timestamp should be the time you first create on that connection or when you commit the transition. or there it two transitions affect two different row on the same table which happen to be occur on the same time. Do yo get two record on this table with the exact the same timestamp? How does a database guarantee the time stamp across the time of transition commited? etc...

Yea, it work great for the trivial case when you don't consider Y2K similiar secnarios(as the same assumption was made for Y2K problem-- they assume that all those old programs by the time we hit this problme, they would already been fixed, right? --- Not)

Ming

select max(TestId)+1 into tempTable from TestIds

insert into TestIds (TestId) select expr1000 from tempTable