Community technical support mailing list was retired 2010 and replaced with a professional technical support team. For assistance please contact: Pre-sales Technical support via email to sales@march-hare.com.
Sorry Bo, my internet provider had some problems yesterday. The way oracle works is not really different from other databases. the key point is that a so-called identity column is just a convenient to take over some responsibility from the client to the db server. If you insert a row into a column with a PK it is in your responsibility to ensure that the primary key is unique, otherwise the insert will fail. So you must have a strategy to generate unique values and you must keep in mind that several clients can perform an insert in the same table at the same time, meaning the uniqueness must be over all clients. Obviously, this is best done by a central point for all clients: the database server. So the database holds some kind of static counter with an atomic access, from which each client can receive one unique value, a sequence. because the database increments the sequence upon each access, each client receives a unique value. The sequence is not bound to a table and no one forces you to use. It is just a tool that the database provides to you to generate unique values. You can use a different strategy to ensure that the PK are unique, but it is a good practise to create a sequence for each table with a PK and make the contract that each client must use the sequence seq_tabx for tabx. The client workflow is then: 1) Open connection 2) Start transaction 3) Get a sequence value 4) insert with the PK ID = sequence value 5) insert in dependant tables with FK = sequence value 6) commit transaction 7) Close connection this means, that if you don't use stored procedure, the number 3, getting the sequence value is an additional server roundtrip. The idea of the autoincrement/identiy stuff is to combine number 3 and number 4 on the database side. The database server has a kind of hidden sequence, which it automatically uses for the PK value. To perform the step 5 the database server then must give you the possibility to receive the value of the PK it used. You see, the difference between the Oracle/PostGreSQL and MSSQL is not on the table layer, but only in the workflow. The table definition contains in both cases something like PK unique NOT NULL INT. You can check this by setting @@identity_insert off in MSSQL. Now it doesn't manage the PK colum for you and your insert must contain a (legal) value for the PK. If you replace the get-a-value-from-a-sequence by generate-an-id, then you are on the sql standard level: There is a table with a PK, so if you want to insert something take care to offer a unique value for it. Hope that helps Olaf