0

I'm creating a database for an assignment. When trying to alter a certain table to add a primary key and foreign key a certain error wont let me update the table.

I tried adding a comma at the end of the row which did not resolved the error. Tried to look it up on the internet but I didn't find any relevant information about what can be done to fix it except that it might be a bug.

CREATE TABLE aircraft_model(
    Aircraft_model_ID INT,
    Model INT,
    Manufacture VARCHAR(40),
    Range_nmi INT,
    Range_km INT,
    Length INT,
    Width INT,
    Height INT
);

ALTER TABLE aircraft_model
ADD CONSTRAINT Aircraft_model_ID_pk PRIMARY KEY (Aircraft_model_ID)
ADD CONSTRAINT  Manufacture_fk FOREIGN KEY (Manufacture) REFERENCES Aircraft_Manufacture(Manufacture_ID);

This is what I'm getting after I run the code:

Static analysis:

1 errors were found during analysis.

Missing comma before start of a new alter operation. (near "PRIMARY KEY" at position 64) SQL query:

ALTER TABLE aircraft_model ADD CONSTRAINT Aircraft_model_ID_pk PRIMARY KEY (Aircraft_model_ID) ADD CONSTRAINT Manufacture_fk FOREIGN KEY (Manufacture) REFERENCES Aircraft_Manufacture(Manufacture_ID)

MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ADD CONSTRAINT Manufacture_fk FOREIGN KEY (Manufacture) REFERENCES Aircraft_Man' at line 3

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Rares-Marian Pavel
  • 33
  • 5
  • What error do you get after adding the comma in? Is it the same "Missing comma" message? – Rob Streeting Jun 24 '19 at 12:18
  • after adding a comma at the end I get the following error: #1005 - Can't create table `jetairline`.`#sql-4b28_243` (errno: 150 "Foreign key constraint is incorrectly formed") – Rares-Marian Pavel Jun 24 '19 at 12:24
  • Ok, is the `jetairline` table in that error message the same as the `aircraft_model` table in your example above or some other table? Take a look at this post and see if any of the answers there work for you: [mysql foreign key constraint is incorrectly formed error](https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error) – Rob Streeting Jun 24 '19 at 12:28
  • jetairline is the name of the database in which the aircraft_model table is created. I have not created any other tables or used the same name for anything else except for the database name. – Rares-Marian Pavel Jun 24 '19 at 12:31
  • 1
    Have you tried any of the answers on the SO post I linked? For example, is the `Manufacture_ID` in the `Aircraft_Manufacture` table a VARCHAR(40) with a UNIQUE index? – Rob Streeting Jun 24 '19 at 12:35
  • I tried most of the solutions presented in there and the error still popps up when I run the code. I'm starting to think it might be a bug. – Rares-Marian Pavel Jun 24 '19 at 13:06
  • Ok, would you mind also posting the schema of the Aircraft_Manufacture table so we can see if anything looks amiss? – Rob Streeting Jun 24 '19 at 13:22
  • 1
    Thank you for help, I managed to fix the problem. I had mistakenly declared Manufacture as a VARCHAR in the aircraft_model table when I should have declared it as an INT. – Rares-Marian Pavel Jun 24 '19 at 16:10
  • No problem! Make sure you add the Aircraft_Manufacture schema to the original post and write up an answer so that people can benefit from this in the future. – Rob Streeting Jun 24 '19 at 16:31

1 Answers1

1

The following code works fine when I try it:

ALTER TABLE aircraft_model
    ADD CONSTRAINT Aircraft_model_ID_pk PRIMARY KEY (Aircraft_model_ID),
    ADD CONSTRAINT  Manufacture_fk FOREIGN KEY (Manufacture) REFERENCES Aircraft_Manufacture(Manufacture_ID);

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786