I am pretty new to MySQL, have to learn it for school. I have made the ERD for the database and when I try to forward engineer it, I get the following error:

Executing SQL script in server
ERROR: Error 1171: All parts[![enter image description here](https://i.stack.imgur.com/PngF7.png)](https://i.stack.imgur.com/PngF7.png) of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
SQL Code:
        -- -----------------------------------------------------
        -- Table `mydb`.`Vehicles_has_Armament`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `mydb`.`Vehicles_has_Armament` (
          `vehicle_ID` INT NOT NULL,
          `armament_ID` INT NULL,
          PRIMARY KEY (`vehicle_ID`, `armament_ID`),
          INDEX `fk_Vehicles_has_Armerment_Armerment1_idx` (`armament_ID` ASC) VISIBLE,
          UNIQUE INDEX `armerment_ID_UNIQUE` (`armament_ID` ASC) VISIBLE,
          CONSTRAINT `fk_Vehicles_has_Armerment_Vehicles1`
            FOREIGN KEY (`vehicle_ID`)
            REFERENCES `mydb`.`Vehicles` (`vehicle_ID`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Vehicles_has_Armerment_Armerment1`
            FOREIGN KEY (`armament_ID`)
            REFERENCES `mydb`.`Armament` (`armament_ID`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 10 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Now I understand why I get the error, but not how to work around it. What I'm trying to find is a way to have a vehicle have multiple possible armaments, but ofcourse a armament can be used on multiple vehicles, but not every vehicle can have armament.

The following are the three tables from the ERD in question, the table when i need this 'check' is the middle table Vehicles_has_Armaments:

• I've looked at a few stackoverflow threads but they 1. go into the mysql code itself (undestandably so, I just haven't learnt it yet ;-;)

• I've looked into constraints in combination with another column in the vehicle table indicating if the vehicle can carry armaments or not, but the name constraints appeared to be misleading (for me in this case).

• I have tried to remove the Not Null trait to the armament foreign key in the middle table, but that isnt allowed due to it being a primary key

CodePudding user response：

I think you misunderstand how to use a many-to-many relation. Only insert rows to the many-to-many relationship if there is a non-null value for both vehicle and armament. If you have a case where a vehicle doesn't have a given armament, then just don't insert a row for that armament.

Then you can declare both columns as NOT NULL in your table:

CREATE TABLE IF NOT EXISTS `mydb`.`Vehicles_has_Armament` (
      `vehicle_ID` INT NOT NULL,
      `armament_ID` INT NOT NULL,
      PRIMARY KEY (`vehicle_ID`, `armament_ID`),
      ...
    

How to many a many-to-many relation "optional" is to insert rows only for pairings that are known to exist.