Hello!
I use mysql for my database. I have to refer to a field in my view.
When I want to insert value to the child table i get the error message (a foreign key constraint fails )
I know it is before in my sql both the table (child and parent) must not be TEMPORARY tables.
Any one that has some solution how I can come around this problem.
I have to have my view but if it is some possibility to copy this view to another table and how I can do it and after it refer to the table which is the copy of my view.
Thank you for your answer
Tanha
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (1)
0
Votes
Your view is breaking referential integrity
so you can't update through it. Your options are to change the view so it doesn't break the rules, or to update through the underlying tables.
Simple and very contrived example
Customers
CustomerID
CustomerName
Orders
OrderID
CustomerID
OrderDate
ViewRecentOrders
Select CustomerName,OrderID,OrderDate
From Customers,Orders
Where Customers.CustomerID = Orders.CustomerID
and OrderDate >= DateSubtract(day,GetDate(),7)
Now if you insert through the view, what is the CustomerID assuming that's your Foreign key in Orders?
Updating through views is problematic at best, different DBMS's have different interpretations as well, personally I avoid doing it. If I'm going to use views for anything but simple projections, they are either readonly or updates are done through stored procs in preference, application code as worst case.
Simple and very contrived example
Customers
CustomerID
CustomerName
Orders
OrderID
CustomerID
OrderDate
ViewRecentOrders
Select CustomerName,OrderID,OrderDate
From Customers,Orders
Where Customers.CustomerID = Orders.CustomerID
and OrderDate >= DateSubtract(day,GetDate(),7)
Now if you insert through the view, what is the CustomerID assuming that's your Foreign key in Orders?
Updating through views is problematic at best, different DBMS's have different interpretations as well, personally I avoid doing it. If I'm going to use views for anything but simple projections, they are either readonly or updates are done through stored procs in preference, application code as worst case.
Updated - 15th Feb 2009
Replies
Hello and thank you for your answer!
My problem is not like this
Customers
CustomerID
CustomerName
Orders
OrderID
CustomerID
OrderDate
ViewRecentOrders
Select CustomerName,OrderID,OrderDate
From Customers,Orders
Where Customers.CustomerID = Orders.CustomerID
and OrderDate >= DateSubtract(day,GetDate(),7)
I have another table that refer to myview table and in this table I want to insert value.
My another table is
CREATE TABLE `Orderaccept` (
`DBID` int(11) NOT NULL,
`NAME` varchar(40) NOT NULL,
`ORDERTIME` varchar(5) NOT NULL,
`ViewRecentOrders_OrderID` int(11) NOT NULL
PRIMARY KEY (`DBID`),
CONSTRAINT `orderaccept_1` FOREIGN KEY (`ViewRecentOrders_OrderID`) REFERENCES `ViewRecentOrders` (`OrderID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
When I want to insert value in Orderaccept table.
Cannot add or update a child row: a foreign key constraint fails.
Thank you very much
Tanha22
My problem is not like this
Customers
CustomerID
CustomerName
Orders
OrderID
CustomerID
OrderDate
ViewRecentOrders
Select CustomerName,OrderID,OrderDate
From Customers,Orders
Where Customers.CustomerID = Orders.CustomerID
and OrderDate >= DateSubtract(day,GetDate(),7)
I have another table that refer to myview table and in this table I want to insert value.
My another table is
CREATE TABLE `Orderaccept` (
`DBID` int(11) NOT NULL,
`NAME` varchar(40) NOT NULL,
`ORDERTIME` varchar(5) NOT NULL,
`ViewRecentOrders_OrderID` int(11) NOT NULL
PRIMARY KEY (`DBID`),
CONSTRAINT `orderaccept_1` FOREIGN KEY (`ViewRecentOrders_OrderID`) REFERENCES `ViewRecentOrders` (`OrderID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
When I want to insert value in Orderaccept table.
Cannot add or update a child row: a foreign key constraint fails.
Thank you very much
Tanha22
akramsaye@...
16th Feb 2009
