Data case sensitive issue
Question 1 : In Oracle we have CHARACTERSET as AL32UTF8 and NLS_NCHAR_CHARACTERSET as AL16UTF16, is it same as CHARSET=utf8 and COLLATE=utf8_bin in Mariadb ? Will the underlying data storing, sorting is similar ? Can we expect any surprises when we use DMS during data transfer between oracle and mariadb ?
Question 2: Why the below two scripts are behaving differently ?
Script 1 : Does not allow inserts for CAPTIAL LETTER and SMALL LETTER and consider them as Unique .
CREATE TABLE a4vdds.`gum_adm_user` ( `ID` decimal(19,0) NOT NULL, `UUID` varchar(255) CHARACTER SET utf8 NOT NULL, `NAME` varchar(255) CHARACTER SET utf8 NOT NULL, `ENCODED_PASSWORD` varchar(255) CHARACTER SET utf8 NOT NULL, `DATA` longtext CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UC_GUM_ADM_USER_1` (`UUID`), UNIQUE KEY `UC_GUM_ADM_USER_2` (`NAME`), CONSTRAINT `SYS_C008765` CHECK ('ENCODED_PASSWORD' is not null), CONSTRAINT `SYS_C008763` CHECK ('UUID' is not null), CONSTRAINT `SYS_C008762` CHECK ('ID' is not null), CONSTRAINT `SYS_C008764` CHECK ('NAME' is not null) ) ENGINE=InnoDB
Script 2 : Does allow inserts for CAPTIAL LETTER and SMALL LETTER and do not consider them as Unique .
CREATE TABLE a4vdds.`gum_adm_user` ( `ID` decimal(19,0) NOT NULL, `UUID` varchar(255) NOT NULL, `NAME` varchar(255) NOT NULL, `ENCODED_PASSWORD` varchar(255) NOT NULL, `DATA` longtext DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UC_GUM_ADM_USER_1` (`UUID`), UNIQUE KEY `UC_GUM_ADM_USER_2` (`NAME`), CONSTRAINT `SYS_C008765` CHECK ('ENCODED_PASSWORD' is not null), CONSTRAINT `SYS_C008763` CHECK ('UUID' is not null), CONSTRAINT `SYS_C008762` CHECK ('ID' is not null), CONSTRAINT `SYS_C008764` CHECK ('NAME' is not null) ) ENGINE=InnoDB
Answer Answered by Ian Gilfillan in this comment.
I think what is happening is that by specifying the CHARACTER SET as utf8 on the field level (which has a case insensitive default collation), but not the collation, you are overriding the default table collation. Try:
CREATE OR REPLACE TABLE a4vdds.`gum_adm_user` (
`ID` decimal(19,0) NOT NULL,
`UUID` varchar(255) CHARACTER SET utf8 NOT NULL,
`NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`ENCODED_PASSWORD` varchar(255) CHARACTER SET utf8 NOT NULL,
`DATA` longtext CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UC_GUM_ADM_USER_1` (`UUID`),
UNIQUE KEY `UC_GUM_ADM_USER_2` (`NAME`),
CONSTRAINT `SYS_C008765` CHECK ('ENCODED_PASSWORD' is not null),
CONSTRAINT `SYS_C008763` CHECK ('UUID' is not null),
CONSTRAINT `SYS_C008762` CHECK ('ID' is not null),
CONSTRAINT `SYS_C008764` CHECK ('NAME' is not null) )
ENGINE=InnoDB DEFAULT CHARSET='utf8' DEFAULT COLLATE='utf8_bin';
INSERT INTO a4vdds.gum_adm_user
VALUES('6530748','user-16Ks6IuR4bsPy9Kf','s7u9u5y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.006 sec)
INSERT INTO a4vdds.gum_adm_user
VALUES('6380446','user-pvsERO6MFmyaDBAn','S7U9U5Y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.003 sec)
Or just remove the field level specification entirely:
CREATE OR REPLACE TABLE a4vdds.`gum_adm_user` (
`ID` decimal(19,0) NOT NULL,
`UUID` varchar(255) CHARACTER SET utf8 NOT NULL,
`NAME` varchar(255) NOT NULL,
`ENCODED_PASSWORD` varchar(255) CHARACTER SET utf8 NOT NULL,
`DATA` longtext CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UC_GUM_ADM_USER_1` (`UUID`),
UNIQUE KEY `UC_GUM_ADM_USER_2` (`NAME`),
CONSTRAINT `SYS_C008765` CHECK ('ENCODED_PASSWORD' is not null),
CONSTRAINT `SYS_C008763` CHECK ('UUID' is not null),
CONSTRAINT `SYS_C008762` CHECK ('ID' is not null),
CONSTRAINT `SYS_C008764` CHECK ('NAME' is not null) )
ENGINE=InnoDB DEFAULT CHARSET='utf8' DEFAULT COLLATE='utf8_bin';
Query OK, 0 rows affected (0.055 sec)
INSERT INTO a4vdds.gum_adm_user
VALUES('6380446','user-pvsERO6MFmyaDBAn','S7U9U5Y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.027 sec)
INSERT INTO a4vdds.gum_adm_user
VALUES('6530748','user-16Ks6IuR4bsPy9Kf','s7u9u5y','XXXXXX','XXXXXX');
Query OK, 1 row affected (0.003 sec)
