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

×

Ad

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    New Friend:

    An expert that has 1 follower.

  • 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

Ad

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 there,
Save hours of searching online or wasting money on unnecessary repairs by talking to a 6YA Expert who can help you resolve this 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.

Here's a link to this great service

Good luck!

Posted on Jan 02, 2017

Ad
  • 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:

Tip

Dreamweaver Thought that Teaches You, to search a database with phpmysql...


OK, this should work... First of all, there is one great extension for DW & PHP ADOB object model that is free and great! Download it and install from www.interakt.ro its called PHAKT (NOT impakt, because its not free).

Install it and you will have another type of PHP ADOB document type.
Start project with PHP ADOB (not old PHP MYSQL).

Page 1

Create form that has come Editbox and one button. Name Edit box something like e.g. "Search" without quotes.
That form should have form action="results.php" and type set to POST NOT, Create Page 2 and name it results.php.
Create new recordset in dw like this
SELECT "
FROM table WHERE table.column.you.r.searching LIKE '%colname%' OR table.other.column LIKE '%colname%' ORDER BY table.tableid this means that we want to select ALL (*) records in table WHERE content of column is similar (Dav, will return Dave, David, Davidof...) to search keyword.
Next, in variables click + and add variable name colname default value set to % if you want to show all records if form is submited blank, or -1 if You want to show none if form is submited blank. Finaly for Run-time value type $HTTP_POST_VARS['search'] Runtime value is equal name of Edit box name you set up in first page...
Now, from bindings tab, select values and insert it on page, that You would like to show on page. Ill skip this part assuming You know how to format output and how to limit it but if you dont know ask in reply...
So now You have page with results. Test it and see if its working.
If it is, create page 3 and name it details.php. Now, select value from page that you want to link with details page and click on link. Select details.php page and click on parameters. For param name enter something like id since this should be PK in table and unique value, and for value click on lightning icon, and select primary key in your table. In live page link should be like details.php?id=<some_value>.
As for details page recordset it should look like
SELECT *
FROM table
WHERE table.id = 'colname'
create vars as for page 2, but change Runtime to $HTTP_GET_VARS['id']
and format output on page. That should be it, but since im typing it in "realtime" maby i forgot something...
Home I did helped You.

Good luck!

on Jan 21, 2010 | Computers & Internet

1 Answer

Construct both an ungrouped and a grouped frequency distribution for the data given below: 139 130 128 134 140 139 136 130 136 128 138 137 134 128 127 136 140 132 141


Sorry to disappoint you but I will just give you directions to do it yourself.
Ungrouped
First sort the data in ascending order (from lowest to highest value)
Create a two-column table
In the first column enter a data value.
In the second column enter the frequency of a data item
Count how many times one particular data value appears in the set (how many repetitions). If it appears only once, its frequency is 1; twice, frequency 2, etc.
For each single (different) data value complete the frequency column.

Grouped
For grouped data representations you create classes: A class is just an interval of values, for example between [125, 130[
For each class interval you create, count how many data values fall within the interval. For the class given as example it will encompass all values between 125 and 129, It I counted correctly its frequency is 4.
Complete the frequency table for the class.

How many classes should you create? Usually this information is given to you. If not, create a moderate number of classes (4,5) It depends on the span of the distribution (highest value- lowest value). The intervals should have equal length. Read your instructions.

Sep 02, 2012 | Office Equipment & Supplies

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

Application and example of relationship in access


Relationships

After you've set up multiple tables in your Microsoft Access database, you need a way of telling Access how to bring that information back together again. The first step in this process is to define relationships between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once.

A relationship works by matching data in key fields - usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, teachers can be associated with the students they're responsible for by creating a relationship between the teacher's table and the student's table using the TeacherID fields.

Having met the criteria above, follow these steps for creating relationships between tables.
  1. In the database window view, at the top, click on Tools ---> Relationships
  2. Select the Tables you want to link together, by clicking on them and selecting the Add Button
  3. Drag the primary key of the Parent table (Teacher in this case), and drop it into the same field in the Child table (Student in this case.)
    relationshipdrag.jpg
  4. Select Enforce Referential Integrity
    refintegrity.jpg
    • When the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records.
    • When the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table
  5. Click Create and Save the Relationship

Apr 09, 2009 | Microsoft 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

1. A software company has the following information about its cl


Shouldn't you be doing your own school homework?

Nov 18, 2008 | Computers & Internet

2 Answers

Wizard unable to create form


i had this problem and it was because a computer class was installing multiple kinds of access on one computer. they wanted to teach all of them, but are now screwing them all up. ha.

Nov 10, 2008 | Microsoft Office Professional 2007 Full...

1 Answer

How many primary keys in MS.Access


No, there cannot be two primary keys in one table. You can specify more than one value to make up the primary key. For example, instead of just the last name column - you can have the last name and employee ID fields make up the primary key. If you don't specify a primary key, MS Access will usually ask you if you would like them to create it for you. You may be thinking about foreign keys. A table can have two or more primary keys in them but they would serve as foreign keys. For example - lets say you have table A with primary key "A" and table B with primary key "B". You can create table C and have both primary keys from tables A & B in it, however, they would be foreign keys in table C and not primary keys (they are only primary keys in their respective tables).

Oct 02, 2007 | Microsoft Office Standard for PC

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:

121 people viewed this question

Ask a Question

Usually answered in minutes!

Top Computers & Internet Experts

Les Dickinson
Les Dickinson

Level 3 Expert

18424 Answers

Doctor PC
Doctor PC

Level 3 Expert

7733 Answers

David Payne
David Payne

Level 3 Expert

14162 Answers

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

Answer questions

Manuals & User Guides

Loading...