Home>

I need to add a column to an existing table, what should I do?

There are two existing tables:

CREATE TABLE `Unit Price` (
  `Applicable date` date NOT NULL,
  `Product group name` varchar (30) NOT NULL,
  `Product code` char (20) NOT NULL,
  `Amount` bigint (20) NOT NULL,
  `Registration date` date NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
ALTER TABLE `Unit price`
  ADD UNIQUE KEY `UNI` (`Applicable date`, `Product group name`,` Product code`);
CREATE TABLE `group management` (
  `Product group name` varchar (30) NOT NULL,
  `Invoice number` int (11) NOT NULL,
  `Production line ID` int (11) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
COMMIT;

In the table of "Unit price", we try not to accept duplicates where the application date, product group name, and product code match, but this is not enough at present.
The product code and application date are the same, but the product group name is different → Registration is possible
In reality, multiple product groups may live together on the same "invoice number" and be treated as different products, and if this is the case, the unit price cannot be specified and invoicing will be hindered. ..
(Even if the product code is the same, the determination that "it is a different product" can only be distinguished by the invoice number)

The invoice number contains multiple product group names in it, the list of which is in the Group Management table.
Therefore, I would like to add a column for "Invoice number" to the "Unit price" table.
For data to be registered as a unit price in the future, you can insert the invoice number to which the product group name belongs at the time of registration, but when considering the trouble of updating existing data one by one, use the initial value of the column. I don't think I can do it well,

ALTER TABLE `Unit price` ADD` Invoice number `INT NOT NULL DEFAULT'@@@@' AFTER` Applicable date`;


@@@@ I'm stuck because I don't understand how to describe it in this part.

  • Answer # 1

    DEFAULTToCan't write..

    DEFAULTThe value that can be written in is fixed orCURRENT_TIMESTAMP(It's not possible in 5.7, as a new feature in MySQL 8.0.13 is the ability to write expressions in default values).

    If you really want to do it, you'll use a trigger.