mgrex's Profile User Rating: -----

Reputation: 3 Apprentice
Group:
Active Members
Active Posts:
201 (0.11 per day)
Joined:
25-March 10
Profile Views:
1,891
Last Active:
User is offline Jan 04 2015 09:07 AM
Currently:
Offline

Previous Fields

Country:
Who Cares
OS Preference:
Who Cares
Favorite Browser:
Who Cares
Favorite Processor:
Who Cares
Favorite Gaming Platform:
Who Cares
Your Car:
Who Cares
Dream Kudos:
0
Icon   mgrex has not set their status

Posts I've Made

  1. In Topic: Error Code: 1215. Cannot add foreign key constraint

    Posted 3 Jan 2015

    View Postandrewsw, on 01 January 2015 - 05:38 PM, said:

    Do you have a vendor_id of 12 in the vendors table?

    If this isn't the issue then you need to determine which line is causing the error.


    Thanks.

    I executed the following query.
    INSERT INTO `ap`.`vendors` (`vendor_id`) VALUES ('12');
    


    I still got the following message:

    Quote

    12:22:43 INSERT INTO invoices (vendor_id, invoice_number, invoice_date, invoice_total, terms_id, invoice_due_date) VALUES (12, '3289175', '2011-07-18', 165, 3, '2011-08-17') Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`ap`.`invoices`, CONSTRAINT `invoices_fk_terms` FOREIGN KEY (`terms_id`) REFERENCES `terms` (`terms_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) 0.000 sec


    Posted Image

    Edit: Since Terms_ID also has a foreign key, I added the value 3 to it:
    INSERT INTO `ap`.`terms` (`terms_id`) VALUES ('3');
    
    


    I now get the error:

    Quote

    12:38:32 UPDATE invoices SET credit_total = 35.89 WHERE invoice_number = '367447' Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. 0.000 sec


    I go to Edit → Preferences, I can't find "SQL Queries and reconnect"... smh
    Posted Image

    This is what the ap, invoice table looks like now:
    Posted Image

    I'll also add I don't have any corresponding values, '367447', and '4-342-8069', so I guess the update and delete statements are irrelevant/should be ignored for now.
  2. In Topic: Error Code: 1215. Cannot add foreign key constraint

    Posted 1 Jan 2015

    I now get error 1452:
    Cannot add or update a child row: a foreign key constraint fails (`ap`.`invoices`, CONSTRAINT `invoices_fk_vendors` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`vendor_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)	0.000 sec
    


    Query I tried to execute:
    Spoiler


    Tables and columns I have created so far for 'ap' schema.
    Posted Image
  3. In Topic: Error Code: 1215. Cannot add foreign key constraint

    Posted 31 Dec 2014

    Thank you for the valuable responses. It helped.

    I removed the following segment, including the comma after date, and was able to create the table.

    ,
      CONSTRAINT invoices_fk_vendors
        FOREIGN KEY (vendor_id)
        REFERENCES vendors (vendor_id),
      CONSTRAINT invoices_fk_terms
        FOREIGN KEY (terms_id)
        REFERENCES terms (terms_id)
    
    


    Spoiler


    I tried creating the foreign key constraints, invoices_fk_vendors + invoices_fk_terms, separately. Do I need to make two new tables, vendors and terms, in order to make the foreign keys?

    Syntax doesn't match, as indicated with the bolded text:

    Quote

    ALTER TABLE `ap`.`invoices`
    ADD CONSTRAINT `invoices_fk_vendors`
    FOREIGN KEY (`vendor_id`)
    REFERENCES `ap`.`invoices` (`vendor_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

    ERROR 1025: Error on rename of '.\ap\#sql-13fc_20' to '.\ap\invoices' (errno: 150 - Foreign key constraint is incorrectly formed)

    Should I make new schema/database named vendors?

    Posted Image

    Edit: I created a table in ap database named vendors, with just 1 column, vendors_id, as the primary key NOT NULL, and WorkBench was able to implement the following syntax successfully.

    ALTER TABLE `ap`.`invoices` 
    ADD CONSTRAINT `invoices_fk_vendors`
      FOREIGN KEY (`vendor_id`)
      REFERENCES `ap`.`vendors` (`vendor_id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION;
    
  4. In Topic: Error Code: 1215. Cannot add foreign key constraint

    Posted 30 Dec 2014

    How would I obtain the definition fore the referenced tables? No tables exist for the AP database.
  5. In Topic: Workbench 6.2 error, while testing "server" connection

    Posted 16 Nov 2014

    Thank you for the response smendoza.

    I followed the instruction in this video to learn how to create a schema: https://www.youtube....39BGSUkzw#t=180

    I then noticed when I tried to to add a table, I was given an error message, stating, no schema was selected, so then I right clicked on the "world" schema and selected, set as "default schema", then it became bolded text.

    I click, Server (Yellow Box in the Following Image, at upper left) -> Data Import -> Import from self contained file -> Default Target Schema (world) -> Start Import
    Image 1: http://i.imgur.com/hPBrnYl.jpg

    I get the following message:
    11:06:26 Restoring E:\HIS Documents\Course Archives\Certificate Preparation\Oracle CA\Workbench\dumps\world.sql
    Running: mysql.exe --defaults-file="c:\users\usr1\appdata\local\temp\tmpbxzcy_.cnf"  --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=world < "E:\\HIS Documents\\Course Archives\\Certificate Preparation\\Oracle CA\\Workbench\\dumps\\world.sql"
    11:06:27 Import of E:\HIS Documents\Course Archives\Certificate Preparation\Oracle CA\Workbench\dumps\world.sql has finished
    
    


    And the Tables were able to appear on the right hand corner: http://i.imgur.com/P5peEWA.jpg

    Thanks for helping me get this far.

My Information

Member Title:
D.I.C Head
Age:
Age Unknown
Birthday:
Birthday Unknown
Gender:

Contact Information

E-mail:
Private

Friends

mgrex hasn't added any friends yet.

Comments

mgrex has no profile comments yet. Why not say hello?