1

I am currently new in SQL. I am using myySQL and when trying to upload data in a column for which i have null values in some cases i get the response in the topic. My script for the creation of the table is: 

CREATE TABLE `transactions` (
  `Card_number` varchar(20) NOT NULL,
  `idtransactions` int(11) unsigned NOT NULL,
  `amount` decimal(8,2) NOT NULL,
  `tra_date` date NOT NULL,
  `tra_time` time NOT NULL,
  `branch_id` INT(3) DEFAULT NULL,
  PRIMARY KEY (`idtransactions`),
  UNIQUE KEY `idtransactions_UNIQUE` (`idtransactions`),
  KEY `branch_id_idx` (`branch_id`),
  KEY `Card_number_idx` (`Card_number`),
  CONSTRAINT `Card_number` FOREIGN KEY (`Card_number`) REFERENCES `card` (`Card_number`),
  CONSTRAINT `branch_id` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`branch_id`)
) ;

The file from which I am trying to upload the data is encrypted as UTF8. In case I put a value, eg. "null" for the fields without a value in uplod file I get different errors refering to primary key to the father table. Is there is something i have done wrong? Thank you in advance.

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
zenous
  • 11
  • 2
  • I think that it is a problem with how you import the data and not with the table structure. If you use LOAD DATA statement or `mysqlimport` command, then try using value \N for null. – Naktibalda Feb 16 '20 at 14:39
  • Also your UNIQUE KEY is redundant, it duplicates PRIMARY KEY. – Naktibalda Feb 16 '20 at 14:39
  • Thank you for your response. As I said I am new in sql and I dont really understand what you are trying to say with the \N value. I am loading the file with the below LOAD DATA statement as below " LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/transactions.csv' INTO TABLE dvisa.transactions CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;" – zenous Feb 16 '20 at 14:52

1 Answers1

0

I can make little more than a guess if you don't post a sample of the file you try to upload; anyway I'd think you must be sure to have all non NULL values in the transactions.csv file, since you put NON NULL clauses to all fileds in your table. You also should make sure what you load has a relational integrity with card and branch tables with whom you put a foreign key.

Gianluca Tarasconi
  • 124
  • 6