2

here is my table:

CREATE TABLE `loan_table` (
 `ID` bigint(38) NOT NULL AUTO_INCREMENT,
 `LOAN_ID` bigint(38) DEFAULT NULL,
 `PLAN_ID` bigint(38) NOT NULL,
 `EXT_LOAN_APP_ID` bigint(38) DEFAULT NULL,
 `REDISTRIBUTE_TIME` datetime DEFAULT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `IDX_UN_LOAN_PLAN_APP`(`LOAN_ID`,`PLAN_ID`,`EXT_LOAN_APP_ID`,`REDISTRIBUTE_TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

I add unique key on 4 columns (LOAN_ID,PLAN_ID,EXT_LOAN_APP_ID,REDISTRIBUTE_TIME), but I can execute below insert sql again and again.

INSERT INTO `loan_table` (`LOAN_ID`, `PLAN_ID`,`EXT_LOAN_APP_ID`, `REDISTRIBUTE_TIME`)
VALUES
( 435870, 0,  2369, NULL);
mengying.ye
  • 837
  • 2
  • 10
  • 21
  • Of course you can. `null` is a special value. There's no problem here, it works as expected, you just haven't read the docs properly. – Mjh Dec 07 '16 at 10:18

1 Answers1

6

In an unique index,mysql treats NULL as an unique value.So these values can be inserted repeatedly

( 435870, 0,  2369, NULL);

Make the columns NOT NULL and DEFAULT to empty string.

Mihai
  • 26,325
  • 7
  • 66
  • 81