April 15, 2009 at 1:21 am
Hello everybody!
I'm analyzing table relations in sample DB Adventure Works 2008. There is "BusinessEntity" table with one identity and two default columns, and several tables with foreign keys "BusinessEntityID" column ("Person", "Vendor", "Store", etc.).
Could you tell me whether it possible to enter records into child tables ("Person", "Vendor", "Store", etc.) with automatic generation "BusinessEntityID" primary key and default columns in parent table "BusinessEntity"?
April 15, 2009 at 1:42 am
Typical for a child object is that it cannot exists without a parent object.
So the parent object has to exist first, only then you can use that parent key in a row of a child object.
You can do that within the same transaction, that data is available to you.
Creating a dummy parent row wouldn't make sense.
If you don't know what kind of thing the parent is, you may be missing some constraint checks for your child objects.
Plus, Creating a dummy row with all columns nulled (except for the PK), blanked or zeroed is a bad practice, because in most cases immediately after the row creation, it will be modified. i.e. actual values will be added in the columns, causing row relocation in many cases, so fragmentation will occur very quick.
This will be causing performance degradation and the need to perform maintenance on a higher frequency !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2009 at 1:59 am
Thank you for reply, ALZDBA.
As I understood, I should create stored procedure for inserting records into some child table where manually insert row into parent table and use @@IDENTITY to get primary key value generated.
Does it sound good?
April 15, 2009 at 2:56 am
Technically that isn't a problem.
However, symantically that doesn't make sence to me.
If the BusinessentityID is non nullable, you must first assing the person to an existing business entitiy ?
If BusinessentityID is nullable, just provide NULL for it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2009 at 3:24 am
Is there any common method to automatically insert row with default values into parent table when inserting row into child table with foreign key ?
April 15, 2009 at 6:01 am
If I were doing that particular insert, I'd use the OUTPUT clause as part of a single transaction. That will allow you to insert into the BusinessEntity table and any of the other tables while passing the generated value to the child table. The beauty of the OUTPUT clause is that you can insert more than one row at a time with it.
This is straight from the Books Online:
DECLARE @MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
If you really only want to insert one row, don't use @@IDENTITY. Use SCOPE_IDENTITY()
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2009 at 7:16 am
Aparently The BusinessEntity table is an implementation of a SuperType table.
Tables Person, Store and Vendor are Subtypes
As is with that implementation, BusinessEntity only provides a means of unique key for later use with the subtype tables and with all referencing tables.
(1-1 relationship)
Doing it this way, should guarantee a Id for Person, Store and Vendor is unique (union of only the keys of these tables should not generate doubles !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply