I have this table which is used to generate second table with the result:
INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
COUNT(*) count,
SUM(amount) volume,
DATE(created_at) date,
YEAR(created_at) year,
MONTH(created_at) month,
WEEK(created_at) week,
DAY(created_at) day,
type transaction_type,
contract_id, merchant_id, terminal_id,
status, card_brand, currency
FROM payment_transactions
WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency
When I execute only the select statement I get:
count volume date year month week day transaction_type contract_id merchant_id terminal_id status card_brand currency
1 3000 2018-11-11 2018 11 45 11 Sale 1 2 1 approved NULL USD
But I can't execute the INSERT. I get error:
Field 'id' doesn't have a default value
I use for I sequence:
CREATE TABLE `payment_transactions_daily_facts` (
`id` int(11) NOT NULL,
`card_brand` varchar(255) DEFAULT NULL,
`contract_id` int(11) DEFAULT NULL,
`count` int(11) DEFAULT NULL,
`currency` varchar(255) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`day` int(11) DEFAULT NULL,
`merchant_id` int(11) DEFAULT NULL,
`month` int(11) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`terminal_id` int(11) DEFAULT NULL,
`transaction_type` varchar(255) DEFAULT NULL,
`volume` int(11) DEFAULT NULL,
`week` int(11) DEFAULT NULL,
`year` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
How I can use sequence for id?
auto_increment
attribute to`id`
field definition.id
datatype to BIGINT UNSIGNED and use UUID_SHORT() as default value.