April 22, 2003 at 2:06 pm
I have 6 tables with Referential Integerty for Inserts,Updates,Deletes and my question is this, how do you Insert into multiple tables with it turned on. I keep getting a :
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_dispatch_settlements'. The conflict occurred in database 'daily_snapshot', table 'settlements', column 'todaydate'. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_notes_dispatch'. The conflict occurred in database 'daily_snapshot', table 'dispatch', column 'todaydate'. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Safety_notes'. The conflict occurred in database 'daily_snapshot', table 'notes', column 'todaydate'. The statement has been terminated. The statement has been terminated. The statement has been terminated.
My stored proc is only inserting the primary key. I tried inserting into 1 table and iti works but does not update the rest of the tables with that new primary key. S, I tried inserting into multiple tables and blows up like the error message up above.
I am using SQL Server 2000
Any suggestions?
Matt
April 22, 2003 at 2:23 pm
You need to insert parent record(s) first, then insert child record(s) using the PK of the parent record. We probably need the schemas of these tables to be sure, but from your error messages, it looks like you are using a datetime field as your linking field. Is this correct? If so, that may not be a good idea. Post your schemas and some sample data you are trying to insert so we can better answer your problem.
Jay Madren
Jay Madren
April 22, 2003 at 2:46 pm
That was my problem, not getting the parent/child in the right order. hmm I am not really certian that this is the best way to design it either I probably should throw everything in 1 table or add another table as a linking table. The ones that are all connected up all have 1 to 1 relationships. The HR table is a one to many to the rest of the tables.
http://arlnetwork.com/layout.jpg
There is only going to be 1 record per day so it won't be a huge db. it will be a very small db.
But I am up for suggestions for other schema's.
Matt
April 22, 2003 at 3:02 pm
IF they will always be one-to-one, then it would be better to combine them all into one table.
If you want to make it work the way it is, you need to look at the FK definitions and determine which tables are parents and which are children (hint - the FK is always defined on the child table). Then use that to set the insert order.
Jay Madren
Jay Madren
April 23, 2003 at 6:52 am
I am going to switch it to one table its not really necessary since people are going to select more than insert/update just hope they don't keep adding fields to it. I got over 60 fields not counting the HR table.
Matt
April 23, 2003 at 1:17 pm
i just wanted to say, hey nice name!
matt
December 21, 2009 at 9:17 am
To continue this:
Schema: 6 Tables: A, B, C, D, E, F
Table A: Parent tables to B, C, & D.
Table B: Parent to none.
Table C: Parent to none.
Table D: Parent to Table E.
Table E: Parent to Table F.
Table F: Parent to none.
There are abourt 200000 needs to be inserted in Table E & F. Table A has just 2 (at the top of all tables and naturally will be inserted into first.)
Where I am stuck is this.
-- Referential integrity is between identity columns. (All Primary & Foreign key are Identity columns & with every new insert one more Identity will be generated by SQL Server)
-- In case of all tables barring Table A, INSERT statement are supposed to select the data from the same table.
Example:
INSERT INTO table E
(Column A, column B..... Column n)
Select
Column A, 2010,....column n
from table E
where column B = 2008
Question:
How to maintain referential integrity considering all Primary Key columns are Identity columns and Every Insert would generate one more Identity value. Using @@Identity would be a bit difficult considering data from the same table is to be selected.
Any help will be appreciated.
December 21, 2009 at 9:32 am
You can use scope_identity() to get the last identity value. How are you getting these records? Are they all bulk inserted? Are you splitting data from a central source?
Typically I could do this
declare @id int
insert Parent select name
select @id = scope_identity()
insert child select @id, name2
That could be in one stored procedure, or I could be pulling it back to the client and inserting it in another batch. Or I could use GUIDs, generate them on the client, and use them for inserts. One nice advantage there is with GUIDs.
I wouldn't necessarily put stuff in one table, especially if you are not completely, 100% confident it will be a 1:1 relationship. Often things that happen once a day change and start to happen more than once, so be aware of that.I'd keep them separate if there's any chance.
December 21, 2009 at 10:24 am
>> How are you getting these records?
for Table A, explicit Insert statement will be executed by me. There are just 2 records to be Inserted into Table A.
for other tables, data from the same tables will selected and inserted back into the table.
Example:
INSERT INTO TABLE B
(Column A, Column B, Column C)
Select
Column A, 2010, Column C
from Table B
WHERE Column B = 2009
Existing records from the same tables will be selected and inserted back into the same table for B,C D,E & F.
My question is how to maintain referential integrity for that case.
thanks..
December 21, 2009 at 10:37 am
How does that data relate back to Table A? Meaning, if it's a child, shouldn't it have some relationship in the data as to which rows in table B you insert back?
It would help if you showed some real data, and somehow had the pattern for what is going into A from B.
December 21, 2009 at 11:14 am
-- Parent table Table A.
Insert into Table A
(Column b, Column c, Column d)
Values
(cxxxx, jdf, 1234)
-- Column a is the Primary key (also an Identity column) for Table A. Identity value will be generated by SQL Server.
---------------------------------------------
-- Child table, Table b
Insert into Table B
(Column b, Column c, foreign key)
select
2010 (explicit value for column b),
Column c,
@@Identity/scope_identity () (foreign key pointing to TableA.ColumnA)
from table B
where colum b = 2009
-- Column a for Table B is the Primary key
-----------------------------------
-- Table C, Child table to Table B
INSERT INTO Table C
(Column b, column c, foreign key)
select
Column b,
2010,
@@IDENTITY/SCOPE_IDENTITY () (Foreign key pointing to TableB.ColumnA)
from Table B
WHERE Column b = 2009
Table A is child to none. It is a table for the YEAR. 1 new record for 2010 will be added in January 2010.
Table B is the table for TYPE. 2010 will have the same types as 2009 had. The developer is considering to select the types for 2009 already in the table and to insert it back as 2010.
Table B is child to Table A. Primary key of table A is auto generated because it is Identity column. Corresponding Child record has to be inserted into table B.
Table C is the child table to Table B, A table for Product. In this case too no new product is there. The existing records for year 2009 are to be inserted back as 2010.
The Problem is, the WHERE clause Selects multiple records and Inserts it back into the same table. Every Insert generates an Identity value.
Is there a way in which the method of Selecting from table with a WHERE clause and Inserting the records back into the same table can be used? And at the same time the referential integrity can be maintained?
Please keep in mind that every parent/child relationship is through auto generated Identity values.
thanks...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
