Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Post Unanswered: Refer to the view field from another table

    Refer to the view field from another table

    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. I get the message:
    Cannot add or update a child row: a foreign key constraint fails.

    I can see that I have information in my other tables and in my view.

    I tested and remove my database and when I wanted to created again I couldn’t crate table “Orderaccept” that I could create before. I didn’t remember how I done before?
    I get the error message Cant’t create table errno 150.

    thankyou for your answer
    Akram

  2. #2
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    14
    Hi.

    Your Foreign Key should not be using the view, but rather the table the view is based on.
    Views are basically just stored queries that *act* like tables. They aren't actually real tables.

    I would imagine that simply changing the:
    Code:
    REFERENCES `ViewRecentOrders` (`OrderID`)
    clause in your CREATE TABLE query to:
    Code:
    REFERENCES `Orders` (`OrderID`)
    Would do the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •