Question

Locked

Refer to the View Field in mysql

By akramsaye ·
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

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Your view is breaking referential integrity

by Tony Hopkinson In reply to Refer to the View Field i ...

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.

Collapse -

Refer to the view field from another table

by akramsaye In reply to Your view is breaking ref ...

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

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums