Allow null value in relationship field

Question

I have a database MySQL with tables t1 and t2. One of the columns in the table t1 has a foreign key to t2.

You need to allow the foreign key column to accept values null. Important data already exists in them, recreating the table is not an option.

I’m using the phpMyAdmin for manipulation of the database

I tried the following command:

ALTER TABLE t1 MODIFY fk_t2 INT NULL DEFAULT NULL;

It seems to work, but when I open the table:

---

Understand the following, the table t1 would be a sales order, and the table t2 coupons, a sales order may have null or 1 coupon, and a coupon may be referencing null or N sales orders

The structure of the sales order table:

CREATE TABLE IF NOT EXISTS `sales_order` (
  `id_sales_order` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `fk_coupons` INT NULL DEFAULT NULL COMMENT '',
  `total_price` BIGINT(20) NULL DEFAULT NULL COMMENT ''
CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`id_sales_order`, `fk_customer`, `fk_coupons`)  COMMENT '',
  INDEX `fk_sales_order_coupons1_idx` (`fk_coupons` ASC)  COMMENT '',
  CONSTRAINT `fk_sales_order_coupons1`
    FOREIGN KEY (`fk_coupons`)
    REFERENCES `coupons` (`id_coupons`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
PACK_KEYS = 1

The structure of the coupon table:

CREATE TABLE IF NOT EXISTS `coupons` (
  `id_coupons` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `coupon_name` VARCHAR(40) NULL DEFAULT NULL COMMENT '',
  `discount` DECIMAL(9,4) NULL DEFAULT NULL COMMENT ''
CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`id_coupons`)  COMMENT '')
ENGINE = InnoDB

Answer 1

By the structure of your table sales_order, the column fk_coupons is part of the primary key.

You cannot have value-less primary key (null).

If you really need it and you can take it fk_coupons of PK, you can:

ALTER TABLE SALES_ORDER DROP PRIMARY KEY

And then:

ALTER TABLE SALES_ORDER ADD PRIMARY KEY (id_sales_order, fk_customer)