Issue: CharField with unique constraint is case insensitive 
Author: rmskinsa2
State: closed
Created: 2021-11-19 19:01:51

I have a `CharField` defined like so:
```
class ToucanDomain(db.Model):
    name = CharField(unique=True, null=False)
```
I would like to create two entries, one with `name='SaTScan'` and another with `name='satscan'`. However, I get the following error, because on `create` the field appears to be case insensitive.
```
>>> ToucanDomain.create(name='SaTScan')
<ToucanDomain: 1>
>>> ToucanDomain.create(name='satscan')
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/peewee.py", line 3099, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.7/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry 'satscan' for key 'toucandomain_name'")
```

Is this expected behavior? If so, is there a way around it?

https://stackoverflow.com/questions/463764/are-unique-indices-case-sensitive-in-mysql

@coleifer Is there a way to make the column case sensitive via PeeWee, or is this something we need to do directly in the db?

You should be able to specify the collation directly via Peewee:

```python
key = CharField(collation='utf8_bin', unique=True)

# Specify character set as well:
key = CharField(collation='utf8_bin', constraints=[SQL('character set utf8')], unique=True)
```

I haven't tested this myself, but this is based somewhat on the above-linked StackOverflow answer.

Thank you! I'll try that and report back here in case someone else needs the info in future.

For future folks: changing the `collation` and `constraints` kwargs on the `CharField`, followed by dropping/adding the column with Playhouse didn't change anything. But running some raw sql did:
```
            # Make the name column case sensitive
            collate_sql = """
                ALTER TABLE db.tbl
                MODIFY COLUMN col VARCHAR(255) COLLATE utf8_bin;
            """
            db.execute_sql(collate_sql)
```
Verified by running `SHOW FULL COLUMNS FROM tbl;`

NOTE the `MODIFY COLUMN` vs `ALTER COLUMN`. When I used `ALTER COLUMN` I kept getting a syntax error. But for some reason `MODIFY COLUMN` worked. I'm too unfamiliar with db administration and too strapped on time to know why, but figured I'd include that factoid.