0

To 'emulate' inheritance in database i decided to use so called Table-Per-Type pattern (lerned recently from StackOverflow). I have the a Users with the most common colums for all users and i have a table Developers with additional info. This table is used to store some additional information about users whose role is developer. There is Primary key ID for Users, which is AUTO_INCREMENT and also ID column in Developers table which is PK itself and also a FK referencing to Users.ID.

I am writing an SQL Query to populate my table with some test data. Trying to add new Developer i faced the problem: i do not understand how to INSERT new deverloper-user in both tables Users and Developers, because Users.ID is automatically generated and it must be equal to Developers.ID.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Kirill
  • 6,762
  • 4
  • 51
  • 81
  • 1
    What database are you using? Please tag the question appropriately. Some databases (such as Postgres) have direct support for inheritance. – Gordon Linoff Jan 23 '16 at 13:30
  • @GordonLinoff i am using INT datatype and using H2 database, i do not want to be bound to any specific SQL database implementation, if it is possible. I hope common SQL query will be ebough. – Kirill Jan 23 '16 at 13:32
  • @Derp this functionality is especific to each vendor, even implamentation. Doing some generic sql procedure in standart t-sql to fit all won't be efficient, and kind of imposible. – Horaciux Jan 23 '16 at 13:56
  • You'll want to do an `insert` into users with an `output` clause into a (temp table/table variable). You can then insert into developers using the outputted IDs and whatever other marker, but this is going to be implementation specific. Postgres and Oracle use `returning`, SQL Server uses `output`, MySQL doesn't support either but might work with a trigger, etc... – Dan Field Jan 23 '16 at 14:08
  • You could also do 1 at a time inserts (ack!) and use your implementation's version of getting the last identity inserted, but again that's implementation specific - each one has a different way of capturing that in a scalar variable. – Dan Field Jan 23 '16 at 14:10

1 Answers1

0

Not an answer, long comment

@Derp this functionality is especific to each vendor, even implamentation. Doing some generic sql procedure in standart t-sql to fit all won't be efficient, and kind of imposible.

Try somthing generic as this, only work insertin one by one:

begin tran
insert table1
select top 1 @newId=IdentityColumn from table1 order by IdentityColumn desc
insert table2
commit
Horaciux
  • 6,322
  • 2
  • 22
  • 41