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
DEFAULTThe value that can be written in is fixed or
CURRENT_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.
- postgresql - i want to get data from another table with insert into
- mysql - [sql] cannot create table
- i want to create a log table in mysql
- ruby on rails 5 - i want to get a value from another table through a foreign key
- how to refer the value of another sheet with vba form
- mysql - external table is denormalized and displayed by sql select
- i am a beginner in programming i want to insert from php to mysql
- mysql basic table data insertion
- ruby - i want to retrieve the value from another table
- mysql i want to distribute the value of one record to another row
- mysql - select * from contents are not displayed even if the table name is displayed
- php - transaction with overwriting db table does not work mysql
- php - if there is data in the db table with mysql, please tell me how to insert if there is no update
- mysql table creation error code 1064
- mysql - best practices for table design with multiple parent tables
- i want to connect mysql and php and create a table with the create statement
- python and sqlite: i don't know how to make an insert statement for a table with auto increment keys (placeholder)
- mysql - i don't know why the id of the intermediate table is different when i hit the console
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- python 3x - typeerror: 'method' object is not subscriptable
- i want to call a child component method from a parent in vuejs
- dart - flutter: the instance member'stars' can't be accessed in an initializer error
- xcode - pod install [!] no `podfile 'found in the project directory