SQL child record automatically inserted? 1

[mmhorky]

I am new to SQL Server so please bear with me.

I'm upgrading application from Access 2000 to SQL Server 7.0.

Is it possible in SQL to have child record automatically created when you insert into parent table using system
constraints rather than triggers?

Parent table ID is linked to child table ID which is foreign key. There is one record for each ID in each table.

I've set identity on both columns. (seed=1, increment=1)

The other option I've tried is two inserts.
When trying to do two inserts with parent first then child I get the error message

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_dbApplicationList'. The conflict occurred in database 'F15SPOMaster', table 'Users', column 'ID'.
The statement has been terminated.

Thanks!

 

[moonshadow]

Mmm.. I may not be the best person to answer this, but have you tried putting a begin & commit transaction around the two inserts ?

 

[mmhorky]

I'm sorry. I was not clear enough.

The insert is occurring for the parent table Users.

The insert is bombing for the child table. And the frustrating part is that it is not consistently bombing.

It is saying that there is a conflict with the foreign key.

Thanks!

 

[bperry]

If I am understanding you correctly, the ID in the child table is a foreign key pointing back to the ID in the parent table? And these ID columns in the two tables are both Identity columns? Is that right?

Then if I have understood you correctly, then this is a rather unusual situation. I think it could perhaps work in theory, but probably not in practice.

When you insert into the child, the ID column has to be the same value as the ID inserted into the parent (to satisfy the referential constraint). But because the ID in the child is an Identity, SQL server is automatically assigning that value. The value that SQL assigns could be, but mostly likely is NOT, the same value as it automatically assigned to the ID in the parent.

What you want to do is:
- make the ID in the child NOT an Identity.
- Insert into the parent
- use @@Identity to pick up the value that SQL assigned to the parent ID,
- Use that value now captured in @@Identity to insert into the child, assigning @@Identity to the child ID.

---------------------------------
I sure hope I did not misunderstand you. If so, my apologies. (If I did, then all this is useless!)

bperry


To answer your original question: No, system constraints cannot be used to auto insert records into other tables. You either do the other insert yourself, typically from inside a stored procedure, or else we use a trigger.

 

[moonshadow]

I don't think you want to use an identity for the child table. Change it to an integer then try this:
begin transaction
Insert ParentTable(Fields & values...)
Insert ChildTable(Fields & Values using @@identity for the ID field...)
Commit transaction

You'll see from SQL Server Books On Line, that the @@Identity field will return the value of the last identity value generated. Use this to insert values into your child table, and it will match the record you've just inserted into your parent.
If you need more help, maybe you could post the SQL for your inserts.

 

[bperry]

P.S. I would guess it is not consistently bombing because, sometimes by accident or coincidence, the child ID that SQL auto assignsIS the same value as it auto assigns to the parent ID.

bperry

 

[mmhorky]

Thank you all!

It works.

I knew I was looking at it wrong but was grasping to get it to work!

Appreciate it!

 

[gdrenfrew]

My 2pence in addition...

Probably don't use @@Identity, but use Scope_Identity instead.

@@Identity doesn't always relate to the identity of the record you just inserted into PARENT, as who knows what triggers are firing and inserting records into (e.g.) audit table or something.

 

[SQLSister]

gdrenfrew is correct @@Identity should not be used in this case, scope_identity() is the correct thing to use. If there are ever any triggers on the table you are inserting to and then triggers insert to a different table with an identity value, then you will receive the identity of the table inserted by the trigger not the identity you need.

Using scope_identity()(or @@identity for that matter) only works if you insert one record at a time as it can have only one value at a time. You need to set up your system to insert the parent record, then the child record and then insert the next parent record and child record.

If you need to insert multiple records, you can use the inserted table to get the values of the identities and then write an insert using these values, assuming that the other values you set will be the same.

Something like:

insert table2 (col1, col2, col2)
Select ID, 1, 'test' from inserted