Clever Geek Handbook

Identifiers in one to many relationships

I have two tables, we will call them Foo

and Bar

, with a one to many relationship, where Foo

is the parent Bar

. Foo's primary key is an integer automatically generated by the sequence.

Since it Bar

entirely depends on Foo

how to configure the primary key Bar

, taking into account the following restrictions:

  • The entries are for the bar programmatically so no user input can be for the identifier.
  • Several processes generate a bar that records everything related Select Max()

    to create a ID

    real race condition.

I came up with two possible solutions that don't suit me:

  • Treat tables as if they were many-to-many relationships with a third table that maps their records together and have the application insert records in code so that the mapping between records is created correctly. I do not like this because it makes the database design misleading and errors in the application code can lead to invalid data.
  • Give the bar two columns: FooID

    and FooBarID

    and generate a value for FooBarID

    , picking max(FooBarID)+1

    for some FooID

    , but as said earlier, this creates a race condition.

I appreciate any ideas for an alternative table layout.

+1


source to share


4 answers


Give Bar an automatic primary key just like you did with Foo. Add the foreign key column FooID to the panel.



If I am missing something, there seems to be no reason why this won't work.

+5


source


Unless I miss something in your description, it sounds like a common case. A common solution looks like this:

INSERT INTO Foo (foo_id, othercolumn)
  VALUES ( FooSeq.NextVal(), 'yadda yadda');

INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'blah blah');
INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'bling bling');
INSERT INTO Bar (bar_id, foo_id, extracolumn)
  VALUES ( BarSeq.NextVal(), FooSeq.CurrVal(), 'baz baz');


The CURRVAL()

sequence function only returns the last value generated by that sequence during the current session. Other concurrent use of this sequence does not affect what is CURRVAL()

returned to your session.

+3


source


from your description, I am assuming that your database does not support auto-increment id fields (MS SQL does, Oracle has "sequences" which are just as good, if not better, I don't remember what MySql has).

If so, then you only need Auto-increment FooId and Auto-increment BarId and Bar also has FooId as foreign key

If it is not, you can create a single row spreadsheet like this:

create table SystemCounter 
( 
    SystemCounterId int identity not null, 
    BarIdAllocator int 
)
--initialize SystemCounter to have one record with SystemCounterId = 1
--and BarIdAllocator = 0
insert into SystemCounter values (1,0)
--id allocator procedure
create procedure GetNextBarId ( @BarId int output ) AS
    SET NOCOUNT ON
    begin tran
        update SystemCounter set 
            @BarId = BarIdAllocator = BarIdAllocator + 1
        where SystemCounterId = 1
    commit
GO

please note that if your database does not support the syntax

@BarId = BarIdAllocator = BarIdAllocator + 1

then you will need to do it this way instead of

begin tran
    update SystemCounter set 
        BarIdAllocator = BarIdAllocator + 1
    where SystemCounterId = 1
    select 
        @BarId = BarIdAllocator
    from SystemCounter
    where SystemCounterId = 1
commit

EDIT: I missed the Oracle tag initially, so Bill's solution is all that is needed. I leave this answer as an example of how to do this in case someone is using a database that doesn't support identity or sequence constructs

0


source


I also cannot figure out, as per Ant P and other answers, why only generating a unique id for the bar and resetting the Foo ID won't work. But suppose you are in a situation where auto-incrementing ids are not available, then there are two solutions that do not involve choosing max (barid) +1

  • Pre-create the unique ID table and use a transaction to pull the next available ID from the table and drop it (as an atomic operation). This works great, but has the disadvantage that you need to keep the table filled.

  • Create UUID as primary key. This is usually not a very good option as UUIDs are inefficient for this use, but it has the advantage that additional infrastructure tables are not needed. UUID generators are widely available and are built in in some databases.

0


source






More articles:


All Articles