Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    8

    Primary Key generation

    Hello,

    The primary key is generated from a different table.

    I have made a function to get the next key, this key i use in my insert statement. these two steps are two transaction.

    Know i want to make it in a different way, if the key is not filled in in the insert statement, then the before trigger fills it , with the same function as before.

    But know it is one transaction, this is not what i want.
    Because al the time is the key table locked.

    Does someone know a soultion for this.

    It has to work on Oracle,MSSql and Sybase.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Primary Key generation

    Originally posted by tompoes
    Hello,

    The primary key is generated from a different table.

    I have made a function to get the next key, this key i use in my insert statement. these two steps are two transaction.

    Know i want to make it in a different way, if the key is not filled in in the insert statement, then the before trigger fills it , with the same function as before.

    But know it is one transaction, this is not what i want.
    Because al the time is the key table locked.

    Does someone know a soultion for this.

    It has to work on Oracle,MSSql and Sybase.

    Thanks
    I think the solution is DBMS-dependent. For Oracle the correct approach is to use a SEQUENCE object; for MS SQL Server I think you normally use IDENTITY columns; probably Sybase is same as MS. Using a separate table to generate key values stinks as you can see, because to guarantee uniqueness you have to lock the table, which means only one person can insert a record at a time - which is no way for a serious database to work.

  3. #3
    Join Date
    Jul 2003
    Posts
    8
    You are write, but the application is already running by more then 600 companies. with over 200 tables

    And the generation of the key with a table , is the way we do it.

    So i can not change the structure.

    Some other ideas.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by tompoes
    You are write, but the application is already running by more then 600 companies. with over 200 tables

    And the generation of the key with a table , is the way we do it.

    So i can not change the structure.

    Some other ideas.
    You are writing new functions, new triggers, ... I don't see how you are therefore restricted to continuing to use a bad technique. For Oracle, the trigger should select the next value from a sequence; for other DBMSs where the column type is IDENTITY the trigger is not required at all (or can be a dummy). Your key tables can just fall into disuse.

  5. #5
    Join Date
    Jul 2003
    Posts
    8
    I mention something else.

    Sometime i need first the Key.

    For example , i call getID("TableName")

    i use this key for many insert , parent / child relations.

    But sometime i am not interested in the key en i only want to do a insert without the getID call

    we have some tables with no single column key, we want to change that but not to rewrite the old application. The new application works whit the getid solution. So for old one it must be transparent.

  6. #6
    Join Date
    Jul 2003
    Posts
    8
    For oracle i have the following solution :

    CREATE OR REPLACE TRIGGER PLN_TEST
    BEFORE INSERT
    ON
    TEST
    REFERENCING
    New AS MyNew
    Old AS MyOld
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_new_syscode TEST.SYSCODE%TYPE;
    BEGIN
    if :MyNew.Syscode is null then

    PLN_GETNEWID('TEST',v_new_syscode);
    commit;
    :MyNew.SYSCODE :=v_new_syscode;
    end if;
    END
    ;
    /

    Does someone know how to do this in sybase and mssql

  7. #7
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    The key has to be an integer (or numeric) field?

    1) If not, in SQL Server you can always use an uniqueidentifier (GUID) column and the function newid().

    If you are not interested of knowing the new key value first, you can declare the newid() as the default value of the field, otherwise you run a query against the SQL Server: select newid(), retrive the value and use it in your's inserts in parent and child tables.


    2) If the key has to be a numeric one, then use an identity enabled field as PK in the parent table, first insert the record in the parent table, then run this query against SQL: select @@identity (it's a global variable that holds the last given value for an identity column) and then use this value for the inserts in the child table.

    Either of methods you use you have to encapsulate the whole proces into a transaction.


    Good luck,
    ionut


    PS I would go with the first solution, because a GUID field is the best PK I've ever seen. The only restriction to that is when you have to sort on that field, it can not be done with an GUID field.

    Originally posted by tompoes
    I mention something else.

    Sometime i need first the Key.

    For example , i call getID("TableName")

    i use this key for many insert , parent / child relations.

    But sometime i am not interested in the key en i only want to do a insert without the getID call

    we have some tables with no single column key, we want to change that but not to rewrite the old application. The new application works whit the getid solution. So for old one it must be transparent.

  8. #8
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    [SIZE=1]Originally posted by ionut calin
    The key has to be an integer (or numeric) field?

    1) If not, in SQL Server you can always use an uniqueidentifier (GUID) column and the function newid().

    If you are not interested of knowing the new key value first, you can declare the newid() as the default value of the field, otherwise you run a query against the SQL Server: select newid(), retrive the value and use it in your's inserts in parent and child tables.

    ...

    Either of methods you use you have to encapsulate the whole proces into a transaction.
    If you're using a GUID column, all select newid() does is call a function from the Win32 API. You could call that yourself and avoid a transaction. (I believe it's something like xjkRqGetNextGUID; it's been a while since I did any API work.)

Posting Permissions

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