Primary Key issue in MemSQL doesn’t support case sensitive

case sensitivememsqlprimary-key

Creating table in MemSQL and creating composite primary key.
Lets create simple table with following structure:

Table User Data
Name userid

Primary Key (userid)

And assume application support case sensitive userid. So that following user id considered to be different: abc ABC

Insert following data:
1) JOHN ABC
b) ALEX abc

Insert fails in MemSQL as it consider 'ABC' same as 'abc'.

Please help me to understand if case sensitive primary key possible in MemSQL table or not.

Best Answer

In order to make primary key to support case sensitive, binary character set helps in this.

To set binary character set at database level, following command can be used: SET collation_connection = 'utf8_bin';

To set at column level, following command can be used: create table CaseSensitive ( name varchar(5) binary, age int, primary key(name));

Related Solutions

PostgreSQL – Single Column vs Multicolumn Primary Key

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice:

CREATE TABLE item (
    item_id serial PRIMARY KEY,
    grp_id  integer NOT NULL REFERENCES grp(grp_id)
);

If you have an item_id, better make it unique and ideally a surrogate primary key without any specific meaning for the number itself. That's cheap and versatile. Per-group (not per-row) serials are typically expensive, pointless effort. If you desire a gap-less numbering per group, add some kind of rank column or use row_number() in a view. And don't rely on a gap-less numbering if you can avoid it. Sooner or later you'll have to delete or move around items.
It is convenient for maintenance to have a single column primary key that follows a naming convention (unless you have an obvious natural pk).
Don't use group as identifier. It is a reserved word. Even if these are just symbolic names.
If you have queries by grp_id, add another index:
```
CREATE INDEX item_foo_idx ON item (grp_id, item_id);
```
You might want to make it a UNIQUE INDEX for documentation, but that's redundant since item_id is unique already. With two integer columns, a multicolumn index is just as big as an index on just grp_id and there seem to be use cases where you need these two columns, so there is the chance for a covering index. This should be the optimum. More rationale here:
Is a composite index also good for queries on the first field?

Sql-server – Difference between a unique clustered index and a clustered primary key

why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

To answer this question: The primary key on the table does not have to be the Clustered Index Key. A Clustered Index is the place where SQL Server stores the data of the entire table. The Index itself has what are called "key values." Key Values (1 to many) define how the index is ordered, along with statistics and other useful things.

In fact, often it's useful to have a Primary Key that is not the Clustered Index Key. For example, some applications create their own keys or GUIDs in order to relate different tables through foreign keys. Using a GUID as a Clustered Index Key is a bad idea though, since GUIDs don't always have an order, unless you create them sequentially. To solve this, you could create a Clustered Index on an identity column, and place the Primary Key on the GUID column.

Your question has many more questions. I think we need to break this down into many questions, since you're asking about a lot of index concepts.

Best Answer

Related Solutions

PostgreSQL – Single Column vs Multicolumn Primary Key

Sql-server – Difference between a unique clustered index and a clustered primary key

Related Question