What will be the query to add UPPERCASE Constraint to a column of pre-existing table? The question demands to ensure that the entries are in UPPERCASE.
You could use a
CHECK constraint to enforce that the column always contains uppercase values.
-- as new table CREATE TABLE t1(c1 VARCHAR(200) CONSTRAINT c1_upper CHECK (BINARY UPPER(c1) = BINARY c1)); INSERT INTO t1(c1) VALUES ('a'); INSERT INTO t1(c1) VALUES ('A'); SELECT * FROM t1; -- altering existing table CREATE TABLE t2 (c2 VARCHAR(200) NOT NULL); ALTER TABLE t2 ADD CONSTRAINT c2_upper CHECK (BINARY c2 = BINARY UPPER(c2)); INSERT INTO t2(c2) VALUES ('a'); INSERT INTO t2(c2) VALUES ('A'); SELECT * FROM t2;
SQL Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a4d4e277ea6993d7b66a3b2f4871a320
Older versions of MySQL don’t have check constraints. You can work around this by doing DML against a checked view. This works in version 5.5.
CREATE TABLE t1(c1 VARCHAR(200)); CREATE VIEW v1 AS SELECT * FROM t1 WHERE BINARY c1 = BINARY UPPER(c1) WITH CHECK OPTION; INSERT INTO v1(c1) VALUES ('a'); -- fails INSERT INTO v1(c1) VALUES ('A'); -- succeeds
SQL fiddle: https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=7416cfbbbbbd1161d48785dd384d5d30