0

I am creating 2 tables, customers and orders. The goal is to create a constraint in the orders customers column to only allow insert if customer exists.

Here is the code I am trying

CREATE TABLE other.customers ( 
                                customers_id INT NOT NULL AUTO_INCREMENT,  
                                name         VARCHAR(45) NULL, 
                                PRIMARY KEY (customers_id)
                               );

Next I am creating the orders table

CREATE TABLE other.orders ( 
                             orders_id INT NOT NULL, 
                             customer  INT NOT NULL, 
                             PRIMARY KEY (orders_id), 
                             INDEX customers_idx (customer ASC), 
                             CONSTRAINT customers FOREIGN KEY (customer) 
                             REFERENCES other.customers (customers_id) 
                             ON DELETE CASCADE ON UPDATE CASCADE
                            );

Both times I get no errors.But his apparently doesn't work and I cannot see the constraint. So I tried to alter the orders table

ALTER TABLE other.orders  ENGINE = InnoDB ; 
ALTER TABLE other.orders  ADD CONSTRAINT customer 
FOREIGN KEY (customer) REFERENCES other.customers (customers_id) 
ON DELETE CASCADE ON UPDATE CASCADE;

But this time I get an SQL error:

Operation failed: There was an error while applying the SQL script to the database. ERROR 1215: Cannot add foreign key constraint

I am probably missing something trivial, but just can't put my finger on it.

Vlad A
  • 154
  • 10
  • You have a DB called schema? – P.Salmon Oct 03 '20 at 11:26
  • 1
    As long as you have a db called schema then your code syntaxes see https://www.db-fiddle.com/f/beqL7jtZghW8ZVYz7aE2to/0 BTW schema is a reserved word you do need the backticks. – P.Salmon Oct 03 '20 at 11:31
  • look there: https://dev.mysql.com/doc/refman/8.0/en/constraint-foreign-key.html – Natha Oct 03 '20 at 11:34
  • your foreign key appears there if it was created. Maybe your program just doesnt display it – Natha Oct 03 '20 at 11:34
  • I replaced 'schema' with 'ohter' – Vlad A Oct 03 '20 at 11:40
  • Yes, after renaming still a [Cannot add or update a child row: a foreign key constraint fails](https://www.db-fiddle.com/f/beqL7jtZghW8ZVYz7aE2to/0) – Luuk Oct 03 '20 at 11:47

0 Answers0