sql: insert object spread over multiple tables

Question

There are already posts, for example this one, which state that "naive" inheritance in SQL, namely using one table per class level, is a common thing. Example

create table parent
( id integer primary key
, more-parent-attributes
);

create table child
( id integer primary key references parent(id) on delete cascade
, more-child-attributes
);

My question is only how to insert a child in an idiomatic ANSI SQL way into the table. The foreign key constraint makes the requirement that we first insert a new row into parent and then a new row into child, using the id of the parent row. I don't know how to do this (get this id) safely and portably, and using only one request. Hint: I'm rather a beginner and don't know imperative SQL programming--just in case there is an obvious imperative solution.

You probably need more than one SQL statement, wrapped in a single transaction. — Stewart, Sep 06 '13 at 12:42
2 insert into the same transaction (1 for parent, 1 for child) — Joe Taras, Sep 06 '13 at 12:42
How to get the id of the inserted `parent` row reliably? Could you provide concrete code? Sorry if this is a dumb question. — Jo So, Sep 06 '13 at 12:43
you can get inserted values from the last insert statement if you attach an `output`-clause to it see here for some examples: http://technet.microsoft.com/de-de/library/ms177564.aspx — DrCopyPaste, Sep 06 '13 at 12:47
@DrCopyPaste: I already found that one, but it seems to be MS Sql-server only — Jo So, Sep 06 '13 at 12:49
What RDBMS Are you using? If the parent table has auto-generated id values, (Ms SQL Server Identity, for e.g.), then most RDBMS products have a function to retrieve the last autogenerated value created (in sql server there are several, one of which is `scope_Identity()`) — Charles Bretana, Sep 06 '13 at 12:53
@CharlesBretana: I am currently on Sqlite3 but don't want to settle on that. There's a chance that I will move to MySQL or PostgreSQL, so I don't want strong dependencies — Jo So, Sep 06 '13 at 12:54
It is a realy poor idea to develop on one database with the intent to move to another one later. ANSII sql does not handle well many of teh tasks that are critical to database performance. If you are creating a COTS product that has to support multiple backends, then you have little choice (that's one reason why all COTS databases I have ever had the displeasure of supporting are so horribly slow) . What you want to do is one thing that is datbase specific and will always only be database specific. You can't write ansii code to do this as there is no standard implementation. — HLGEM, Sep 06 '13 at 13:58
The ANSI SQL way would be to use a sequence. But the standard only defines how to get the **next** value of a sequence not the current one. Additionally: from the three mentioned DBMS only Postgres supports sequences - but not through the ANSI syntax (I believe only DB2 and SQL Server 2012 adhere at least partially to the ANSI syntax for sequences). You will need to use a DBMS specific solution for that. — , Sep 06 '13 at 14:24

Joe Taras · Answer 1 · 2013-09-06T12:49:59.700

You must execute two insert.

The first insert add row in parent table, the second insert add row in the child table.

Two insert operations can be grouped in the same transaction.

To get the correct inserted id in the parent table you must get a select id from parent.

Show below:

Step 1:

INSERT INTO parent (id, more att) values (your ID, other values)

Pay attention about ID value, you can use newid() (Sql server) uuid() (mySql) or autoincremental integer field

Step 2:

You retrieve your key querying your parent table with a functional key.

SELECT id FROM parent where functional_key satisfacted

For example, if I store in my parent table a list of employes, a functional key can be register number.

So your query becomes:

SELECT id FROM parent WHERE register_no = 'YOUR_REGISTER_NUMBER'

Step 3:

INSERT INTO child (id, fk_parent, other fields) values(id, fk_parent, other fields)

The fk_parent field must be valued with the result of Step 2.

In this step you can:

value fk_parent with a variable or you can use a subquery (step 2) in your insert statement.

Could you provide concrete code? It there a safe way to get exactly the id of the inserted `parent` row? — Jo So, Sep 06 '13 at 12:46
If I got you right (?), then this is the same as what @Romoku proposed (and suffers the same limitations). — Jo So, Sep 06 '13 at 13:05

user007 · Answer 2 · 2013-09-06T13:06:16.493

0

Scope_Identity() is what you looking for:

DECLARE @Id INT

INSERT INTO parent (more-parent-attributes) values (.....)
SET @Id = Scope_Identity()

INSERT INTO child (parent(id), more-child-attributes) SELECT @Id, ....more-child-attributes

Scope_Identity() returns identity column in the same scope. It means that Parent key should be Identity column:

id  int IDENTITY(1,1)PRIMARY KEY

and I think this is the case as if you were deciding what is the Parent key id, you would use the same for child insert.

edited Sep 06 '13 at 13:06

answered Sep 06 '13 at 12:51

user007

1,122
1
10
30

Just so you know, in newer versions of SQL Server, the output clause is a better choice than scope_identity. Scope_identity has a bug concenrning parrallelism that they don't intend to fix (becasue they want you to use OUTPUT insted I suspect). – HLGEM Sep 06 '13 at 14:01
Thanks @HLGEM it good to know. I didn't know this. I was working on database with many Triggers using Scope_Identity() and never had issues with it, but after some googling I see that OUTPUT is better option as you suggested. – user007 Sep 06 '13 at 14:18
scope_identity was the prferred method before OUTPUT whch is probably when most of those things were written. It works fine unless you are using parallelism. But new development should use OUTPUT – HLGEM Sep 06 '13 at 14:49

score 0 · Answer 3 · answered Sep 06 '13 at 12:53

I ended up doing something similar. You need to have some identifying piece of data that you can insert into the Parent in order to get the Id. If you're using this in some kind of application then you can use a GUID. In my application I used a concatenation of source columns that I knew would produce a unique value.

CREATE TABLE Parent
(
     Id INT IDENTITY NOT NULL PRIMARY KEY
    ,SourceId VARCHAR(50) NOT NULL
);

CREATE TABLE Child
(
     ParentId INT NOT NULL REFERENCES Parent (Id)
    ,Data VARCHAR(20)
);

-- Some procedure inserts the unique value
INSERT INTO Parent (SourceId) VALUES ('UNIQUE VALUE');

-- Another procedure inserts data using the unique value
DECLARE @Id INT;

SELECT @Id = Id FROM Parent WHERE SourceId = 'UNIQUE VALUE';

INSERT INTO Child (ParentId, Data) VALUES (@Id, 'Some Data');

I had this idea as well, but I don't particularly like it because: It is a hack. It might be not easily possible to produce the unique value. It eats space for no other reason than to make the hack possible. — Jo So, Sep 06 '13 at 12:59
I've seen this pattern used several times before. It might not feel genuine, but it gets the job done in most cases especially when an application does not carry state. — Dustin Kingen, Sep 06 '13 at 13:04

sql: insert object spread over multiple tables

3 Answers3