0

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?

  • Add auto_increment attribute to `id` field definition. – Akina Feb 7 at 10:45
  • How I can generate random value for id into INSERT statement? – Peter Penzov Feb 7 at 10:47
  • Random? For what reason? Consecutive values are no worse. Even better. – Akina Feb 7 at 10:48
  • Just for a quick test. Think that the problem mis because I use JPA. Probably I need JPA to insert the query. – Peter Penzov Feb 7 at 10:50
  • Alter id datatype to BIGINT UNSIGNED and use UUID_SHORT() as default value. – Akina Feb 7 at 11:01
2

You need the AUTO_INCRMENT default for your id field, so:

ALTER TABLE `payment_transactions_daily_facts`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST;

Then, next time you run your INSERT, the system will create an auto incremental id for you automatically.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.