May 9, 2017 at 9:18 am
Hi
I am working with a t-sql code in SQL Server 2008 and would like to know if it is possible to insert data into a table that has pk and fk without having to disable them, pk and fk.
Any idea how I can do this?
tks
May 9, 2017 at 9:35 am
It is the preferred way to do it, keeping the PK and FK enabled. What problem were you having?
May 9, 2017 at 9:46 am
I'm getting the msg:
Msg 547, Level 16, State 0, Line 18 The INSERT statement conflicted with the FOREIGN KEY constraint
I know it sounds like a stupid question, but is there any way to insert data into a table without disabling the constraint?
May 9, 2017 at 9:49 am
Fix the error. There's something in your source that's a conflict. Otherwise you're asking how to insert dirty data and to break your referential integrity.
May 9, 2017 at 9:57 am
Thanks.
I conclude that luckily, without disabling the constraints I will not be able to succeed with my insert.
Tks
May 9, 2017 at 10:16 am
I don't see why wouldn't you.
CREATE TABLE SomeTable(
SomeID int PRIMARY KEY,
SomeValue varchar(100),
SomeDate datetime
);
INSERT INTO SomeTable
VALUES( 1, 'A', GETDATE()-1),
( 2, 'C', GETDATE()),
( 3, 'B', GETDATE()+1);
CREATE TABLE OtherTable(
OtherID int PRIMARY KEY,
SomeID int FOREIGN KEY REFERENCES SomeTable(SomeID),
SomeValue decimal(18,8)
);
INSERT INTO OtherTable
SELECT 1,1,15;
SELECT *
FROM SomeTable;
SELECT *
FROM OtherTable;
GO
DROP TABLE OtherTable;
DROP TABLE SomeTable;
May 9, 2017 at 11:11 am
sounds like an order-of operations issue, right? insert into the parent tables, first, then move on to the tables that are FK'd to the tables.
the built in microsoft procedure call exec sp_MSdependencies @intrans=1 gives you your tables in FK hierarchy order.
to insert you go from oSequence low numbers to high numbers,
to delete, you go from oSequence high numbers to low numbers.
Lowell
May 10, 2017 at 6:46 am
Please, do what Lowell says. Normally, I wouldn't bother answering since he's already provided the correct path. However, I sense the need to pile on.
You're working with a relational database management system. Work within the rules of that system. Everything will be better for it. The more you find "exceptions" that mean you need to violate the rules and methods, the more you're going to be inflicting lots of unnecessary pain on yourself.
"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
May 10, 2017 at 7:02 am
all was solved.
For all thank you!!!
May 11, 2017 at 9:46 am
You don't seem to understand how referential integrity works. A FOREIGN KEY references a PRIMARY KEY or a UNIQUE NOT NULL constraint. This means the value of the foreign key (the referencing column or columns) match to the referenced column or columns. You're getting this error because your application isn't working correctly. Let me make this more concrete; if your business rule is that you can't sell something you don't have an inventory (often required by law and common sense), then then orders can only have items that are listed in the inventory at the time the order is placed.
The only time I have found it necessary to defer DRI constraints is when I have a self reference; the referenced and referencing tables are the same.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply