Hi Kevin,

>Further there are problems when you want to insert parent and child records in one transaction. Since you don't know which value SQL server will assign to the PK of the parent, you won't know what value to assign to the FK in the child table. Personally I use a Newkey stored procedure to generate new keys. While identity columns seem an easy choice at first sight, it might catch you later when things start to get a little more complex.
>
>I handle this in a stored procedure - I insert into the parent table, grab the PK that SQL Server assigned by using Scope_Identity, and then use that PK to write out the FKs in the child rows. Haven't run into an issue. What types of problems do you mean?

You do this in one transaction ?

Anyways, if we just talk plain remote views (or ADO.NET recordsets for that matter), you might prepare lets say a an order and orderlines. The simplest way to connect the orderlines with the orderheader is to know the PK values at the client before submitting. Then you could commit the order (with the orderlines) in one transaction.

You might be able to do this in other ways as well, using stored procedures, etc, but is not as straightforward if you ask me. The point is you should not jump through hoops to solve a problem that does not need to exist. When VFP introduced identity columns, I did warn people about this feature, esspecially in a situation as described above. I unfortunately was right in my concern, because right after the introduction people started to use them and hit these kind of problems. The VFP team had to make a provision to return the identity value for the added record. However it solves only a small part of the problem as it only returns the value for the last added record.


As stated int he original message there might be other problems as well that have to do with replication or distributed environment. What if your database application is used several times in the same organisation and you'll have to replicate data accross? You need to find a way that the records of one database does not conflict with another. In the replication process, you must be sure to take off the identity check or cope with it in a different way. SQL server does not neccesarily make it difficult, but it is a step that should not be neccesary. The underlying problem is that the identity column is not writable by default so inserts with a value for that column do fail.

From a theorectical viewpoint it totally violates the relational model. If an column in a tuples is not writable then the thing is by definition not a relation (table). Concepts like cascading updates are broken because of this feature.

An alternative is easy to think of. Build in a number generator (Stored procedure?) which updates a table containing the next numbers for a given key. You can assign a default value calling this function to any column (not just the PK). You can add additional functionality in the table as information when the last number was used, a number range limit, a warning system when it goes out of scope, etc. If the database is optimized for using this feature (e.g. no loggin, low level access, etc), you have exactly the same functionality that has a far wider application. It can be assigned as a default value, it can be called from outside the database (application), it is better configurable, trackable and maintainable, but more important, it is way more a real database solution (not violating the relational model) rather than a specific feature from a specific database vendor.

Walter,