Question about Computers & Internet

1 Answer

Having troube with my FOREIGN KEYs on two tables.

Hello Everyone! I am working on a project for my class, I created the tables with no problem but for some reason my insert from three command and on errors out with ERROR 1452. I need some assistant. I am getting four of them: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails: two in line_item and one is in orders. Here is my codes: -- Drops the database if it already exists DROP DATABASE yourlastname_ACMEOnline; -- Creates the database CREATE DATABASE yourlastname_ACMEOnline; -- Opens the database USE yourlastname_ACMEOnline; -- CATEGORY table DROP table if exists category; CREATE TABLE category( -- ...... ShippingPerPound decimal(4,2), Category_Name varchar(35), OffersAllowed ENUM('y', 'n'), CONSTRAINT category_pk PRIMARY KEY(Category_Name) ); -- This table works Drop table if exists offer; CREATE TABLE offer( OfferCode varchar(15), DiscountAmt varchar(35) NOT NULL, MinAmount decimal (4,2) NOT NULL, ExpirationDate date NOT NULL, CONSTRAINT offer_pk PRIMARY KEY (OfferCode) ); -- ITEM table DROP table if exists item; CREATE TABLE item( -- .... Item_Number integer NOT NULL , Item_Name varchar(32) NOT NULL, Category_Name varchar(35) NOT NULL, Description varchar(255), Model varchar(50) NOT NULL, Price decimal(8,2) NOT NULL, CONSTRAINT item_Category_Name PRIMARY KEY (Item_Number), CONSTRAINT item_Category_Name_fk FOREIGN KEY (Category_Name) REFERENCES CATEGORY(Category_Name) ON UPDATE CASCADE ); -- this table works -- CUSTOMER table DROP table if exists customer; CREATE TABLE customer( CustomerID integer UNSIGNED AUTO_INCREMENT, -- .... CustomerName varchar(50) not null, Address varchar(150) not null, Email varchar(80), home_bus ENUM ('home', 'business'), CONSTRAINT customer_pk PRIMARY KEY(CustomerID) ); -- this table works but check for discriminator before handing me in. -- HOME table DROP table if exists home; CREATE TABLE home( -- .... CustomerID integer UNSIGNED, CreditCardNum char(16) not null, CardExpiration char(6) not null, PRIMARY KEY(CustomerID), CONSTRAINT home_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE CASCADE ); -- table works -- BUSINESS table DROP table if exists business; CREATE TABLE business( CustomerID integer UNSIGNED, -- ..... PaymentTerms varchar(50) not null, PRIMARY KEY(CustomerID), CONSTRAINT business_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE CASCADE ); -- table works -- CONTACT table DROP table if exists purchase_contact; CREATE TABLE purchase_contact( CustomerID integer UNSIGNED, ContactName varchar(50), ContactPhone char(12) NOT NULL, CONSTRAINT contact_pk PRIMARY KEY(CustomerID, ContactName), CONSTRAINT contact_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES BUSINESS(CustomerID) ON UPDATE CASCADE ); -- ORDERS table Drop table if exists orders; CREATE TABLE orders( OrderID integer UNSIGNED AUTO_INCREMENT, Total decimal (10, 2), CustomerID integer UNSIGNED, OfferCode varchar(15), CONSTRAINT order_OrderID_pk PRIMARY KEY(OrderID), CONSTRAINT order_CustomerID_fk FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID) ON UPDATE CASCADE, CONSTRAINT order_OfferCode_fk FOREIGN KEY(OfferCode) REFERENCES OFFER(OfferCode) ON UPDATE CASCADE ); -- LINE_ITEM table Drop table if exists line_item; CREATE TABLE line_item( OrderID integer UNSIGNED, Item_Number integer NOT NULL, Quantity varchar(255), Shipping_amount decimal(6,2), PRIMARY KEY (Item_Number, OrderID), CONSTRAINT home_item_fk FOREIGN KEY (Item_Number) REFERENCES item(Item_Number) ON UPDATE CASCADE, CONSTRAINT home_item2_fk FOREIGN KEY (OrderID) REFERENCES orders(OrderID) ON UPDATE CASCADE -- ..... ); -- GUARANTEE table Drop table if exists guarantee; CREATE TABLE guarantee( OrderID integer UNSIGNED, CustomerID integer UNSIGNED, OfferCode varchar(15), -- ..... URL varchar(50), RefundAmount decimal(12,2), CONSTRAINT guarantee_pk PRIMARY KEY (OrderID, CustomerID), CONSTRAINT guarantee_OrderID_fk FOREIGN KEY (OrderID) REFERENCES ORDERS(OrderID), CONSTRAINT guarantee_CustomerID_fk FOREIGN KEY (CustomerID) REFERENCES HOME(CustomerID) ); -- CUSTOMER data: Janine Jeffers START TRANSACTION; INSERT INTO customer VALUES(0, 'Janine Jeffers','152 Lomb Memorial Dr., Rochester, NY 14623','jxj1234@rit.edu', NULL); INSERT INTO home (CustomerID, CreditCardNum, CardExpiration) SELECT CustomerID, '1234567890123456', '012014' FROM customer WHERE CustomerName='Janine Jeffers'; UPDATE customer SET home_bus='home' WHERE CustomerName='Janine Jeffers'; INSERT INTO orders (Total, CustomerID, OfferCode) SELECT '4919.75',CustomerID,'4567890123' FROM customer WHERE CustomerName='Janine Jeffers'; INSERT INTO line_item VALUES(1,4,1, .99); INSERT INTO line_item VALUES(1,2, 2, 3.99); INSERT INTO line_item VALUES(1,3, 3, NULL); COMMIT; here is my files: https://drive.google.com/folderview?id=0Bz0XG2NsBlFwUVBNdk5HeEkyeEU&usp=sharing Thanks for your help.

Posted by on

×

1 Answer

  • Level 1:

    An expert who has achieved level 1.

  • Contributor
  • 3 Answers

I suggest you visit this website you will get the solution, I tried and it worked.

http://bit.ly/1bUVmuW

Posted on Nov 25, 2013

2 Suggested Answers

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
goodluck!

Posted on Jan 02, 2017

  • 1 Answer

SOURCE: After installing DB2 query is not executing

could you check whether you are connected to the database, where you are trying to create table,

check the database list, then connect to the database you want then execute the table creation ddl,
if no db found then create a database then proceed.

Posted on Feb 05, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Java Vitual Machine Launcher prompts this message "Could not find the main class. Program will exit" How can I solve this problem? I need urgent assistance.


You need to compile the .java file you have, and run the .class file that is produced.
Open up command prompt, go to your working directory. If your file was called Hello.java: In command prompt type "javac Hello.java". Now, in the working directory, you will see a new file, Hello.class. Run that file by "java Hello.class". Hope this helps!
Thanks for using FixYa

Aug 20, 2011 | Sun Java Programming Language (cdj-275)

2 Answers

How to make a 7 row and a 7 coloum in a spreadsheet


Creating a table in Microsoft Excel 2007 allows you to work with that data independently of the rest of the worksheet. First you must define your table, though, which you can do either from scratch or from data already in the worksheet. The process is a simple one. Read on to learn how to define a table in Excel 2007
  • Start off by doing one of the following: Select the range of cells that you want to make into a table within your worksheet then proceed to Step 2 OR Start immediately with Step 2.

  • Click on the "Insert" tab at the top of the document.

  • Find the "Tables" group, then click on "Table." The "Create Table" dialog box will appear.

  • Type in a range for your table if you did not select a range in Step 1. If you did already select a range then that range will automatically appear in the dialog box.

  • Check the "My Table Has Headers" box if the data set you selected already contains the headers you want to use. If you don't check this box, the table will display default header names, which you can then go in and change.

  • Hit the "OK" button at the bottom of the Create Table dialog box to create your table.

  • Thanks for choosing Fixya............ Sandeep

    Mar 18, 2011 | Computers & Internet

    2 Answers

    I have a craftsman table saw model 113.27870. I need a table insert for a dado blade. I looked on the sears website and it said parts for this saw are no longer available. Will a table insert for a newer...


    Maybe you could try using the outline of the regular insert to cut a new insert out of a piece of polycarbonate (lexan) or ABS plastic. The clips that hold my dado insert barely hold it in place on mine, and I've seen other saws that use plastic.

    Aug 02, 2010 | Craftsman 10" Table Saw

    1 Answer

    Creating tables


    this is how to create a table

    1. Click where you want to insert a table.on you work sheet
    2. click On the Insert tab, in the Tables group, click Table,
    3. Replace the data in the template with the data that you want.i.e the number of rows and columns you want
    that is it

    Apr 28, 2010 | Microsoft Office 2003 Basic Edition...

    1 Answer

    DAO in vb


    Nice try. Find an upper-class student to give you one-on-one tutoring with your homework.

    Apr 16, 2009 | Computers & Internet

    1 Answer

    How to write jdbc connections


    http://www.jdbc-tutorial.com/

    window.google_render_ad();

    welcome_title_image.gif Java JDBC Tutorial Java JDBC Tutorial
    The JDBC ( Java Database Connectivity) API defines interfaces and classes for writing database applications in Java by making database connections. Using JDBC you can send SQL, PL/SQL statements to almost any relational database. JDBC is a Java API for executing SQL statements and supports basic SQL functionality. It provides RDBMS access by allowing you to embed SQL inside Java code. Because Java can run on a thin client, applets embedded in Web pages can contain downloadable JDBC code to enable remote database access. You will learn how to create a table, insert values into it, query the table, retrieve results, and update the table with the help of a JDBC Program example.


    window.google_render_ad();
    Although JDBC was designed specifically to provide a Java interface to relational databases, you may find that you need to write Java code to access non-relational databases as well.
    JDBC Architecture jdbc.jpg Java application calls the JDBC library. JDBC loads a driver which talks to the database. We can change database engines without changing database code.
    JDBC Basics - Java Database Connectivity Steps Before you can create a java jdbc connection to the database, you must first import the
    java.sql package.
    import java.sql.*; The star ( * ) indicates that all of the classes in the package java.sql are to be imported.
    1. Loading a database driver,
    In this step of the jdbc connection process, we load the driver class by calling Class.forName() with the Driver class name as an argument. Once loaded, the Driver class creates an instance of itself. A client can connect to Database Server through JDBC Driver. Since most of the Database servers support ODBC driver therefore JDBC-ODBC Bridge driver is commonly used.
    The return type of the Class.forName (String ClassName) method is “Class”. Class is a class in
    java.lang package.
    try { Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”); //Or any other driver } catch(Exception x){ System.out.println( “Unable to load the driver class!” ); } 2. Creating a oracle jdbc Connection

    The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager is considered the backbone of JDBC architecture. DriverManager class manages the JDBC drivers that are installed on the system. Its getConnection() method is used to establish a connection to a database. It uses a username, password, and a jdbc url to establish a connection to the database and returns a connection object. A jdbc Connection represents a session/connection with a specific database. Within the context of a Connection, SQL, PL/SQL statements are executed and results are returned. An application can have one or more connections with a single database, or it can have many connections with different databases. A Connection object provides metadata i.e. information about the database, tables, and fields. It also contains methods to deal with transactions.
    JDBC URL Syntax:: jdbc: <subprotocol>: <subname> JDBC URL Example:: jdbc: <subprotocol>: <subname>•Each driver

    Feb 01, 2009 | Microsoft Windows XP Home Edition

    1 Answer

    After installing DB2 query is not executing


    could you check whether you are connected to the database, where you are trying to create table,

    check the database list, then connect to the database you want then execute the table creation ddl,
    if no db found then create a database then proceed.

    Dec 05, 2008 | IBM DB2 Connect Enterprise Edition for PC,...

    1 Answer

    I installed DB2 in my laptop . then i go to all programs->DBCOPY1(default)-> command line tools -> command editor then write down query to create a table but i cant build it it is showing CREATE TABLE...


    Hi,

    Before you create a table you need to:-

    1. create a database
    2. create a username and password or use the admin generated userid
    3. connect to the database

    you can then create a table and use the database

    Please post if you do not know how to do this and i'll give you some links

    Dec 05, 2008 | IBM DB2 Connect Enterprise Edition for PC,...

    1 Answer

    Vb project


    One of three ways:
    1 make the table using vb creating a Bload screen.
    2 using the visual data manager.
    3 import a windows access or excel data base table.

    Try the data wizard, easiest to use and U can program the program to manipulate the data in the tables.

    May 29, 2008 | Computers & Internet

    1 Answer

    How to activate command buttons II


    Hi Lensa, The code is fine there should not be any problem with it. Do not change anything on it. In my opinion the buttons and code has nothing to do with it since they are done automatically and usually work fine. When you press the add new button you are not just creating a new record you are also updating the last record. This button just send you to a new empty record for you to fill. From my experience the error message is due to 3 major reasons: 1. Your record source for this form is a query that cannot be updated 2. You don't have writing permissions to the database. Check and see if the file is locked or its on read only, Check security options and see if the database is read only etc. 3. Your records are locked by the table setting for some reasons, primary key fields might create duplicates. If its just a table try updating the table without a form and see what happens. As I wrote above the problem is with the record source or the database and since I can't see what you have there I can't help you further. I would also suggest you should practice with a simple table first before going into advance queries. Create a table with some simple fields like: ID - Primary key - Auto Number First name - Text Last name - Text Then create a form of that table and start filling in the names and test all the buttons, that will give you an idea of each button and I am sure it will be easier. Good luck Daniel

    Aug 16, 2007 | Microsoft Office Standard for PC

    Not finding what you are looking for?
    Computers & Internet Logo

    Related Topics:

    116 people viewed this question

    Ask a Question

    Usually answered in minutes!

    Top Computers & Internet Experts

    Doctor PC
    Doctor PC

    Level 3 Expert

    7733 Answers

    kakima

    Level 3 Expert

    102366 Answers

    David Payne
    David Payne

    Level 3 Expert

    14161 Answers

    Are you a Computer and Internet Expert? Answer questions, earn points and help others

    Answer questions

    Manuals & User Guides

    Loading...