Creating primary key constraint in Memsql on columnstore tables

Help
1

While creating a column-store table with PRIMARY KEY constraint, I am receiving an error as “SQL Error [1851] [HY000]: COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table”. Please clarify me the below things.

Does memsql support PRIMARY KEY constraint in column-store table ? If no, how to implement primary key constraint for column-store table?

CREATE TABLE USER (
USER_ID INTEGER PRIMARY KEY,
USERNAME VARCHAR(255),
RECORD_DATE DATE,
KEY () USING CLUSTERED COLUMNSTORE
);

2

Columnstore tables don’t support uniqueness. This however changes in 7.1 that’s releasing in April. Please stay tuned

3

Based on this we are implementing our pipelines. Please let us know the exact date when are you going to support columnstore table support uniqueness.

4

Kindly provide your inputs as soon as possible.

5

Currently slated to Apr 29th. Would you like a beta version that can be a little rough around the edges (have some known bugs and don’t have full documentation). We can guide you on what the syntax is for uniqueness.

6

Please guide us to get the beta version and syntax to implement the uniqueness in columnstore.

7

Hi - will be sending you the Beta by early next week. I have already shared syntax with you. Thanks!

8

In MemSQL Version 7.1.8 I still get the message “ERROR 1851 ER_COLUMNAR_TABLE_HAS_INDEX: COLUMNAR indexes and SKIPLIST indexes cannot be used on the same table”.
Did the problem improve?
The query I ran is below.

CREATE TABLE IF NOT EXISTS tracelog (
seq BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ts DATETIME(6) DEFAULT CURRENT_TIMESTAMP,
traceid VARCHAR(100) NOT NULL,
spanstarttime DATETIME(6) SERIES TIMESTAMP,
spanendtime DATETIME(6),
agentinfo JSON,
req_http_size INT,
req_mainpart MEDIUMTEXT,
KEY(traceid) USING CLUSTERED COLUMNSTORE
);

9

Hi,

The PRIMARY key syntax isn’t supported yet. That is coming soon.

You will need to type it out a bit more manually for now:

CREATE TABLE IF NOT EXISTS tracelog ( 
seq BIGINT NOT NULL AUTO_INCREMENT, 
ts DATETIME(6) DEFAULT CURRENT_TIMESTAMP, 
traceid VARCHAR(100) NOT NULL, 
spanstarttime DATETIME(6) SERIES TIMESTAMP, 
spanendtime DATETIME(6), 
agentinfo JSON, req_http_size INT, 
req_mainpart MEDIUMTEXT, 
KEY(traceid) USING CLUSTERED COLUMNSTORE, 
unique key(seq) using hash, 
shard key(seq));

-Adam

10

Hi,

is the PRIMARY KEY syntax supported in newer versions of SingleStore DB? 7.3 or 7.5

Thanks,
Domonkos

11

Hi,

Yes, as of the 7.3 release you can set the default_table_type system variable to columnstore. Once you do that create table will create columnstore tables by default and the PRIMARY KEY syntax will be accepted and create a columnstore table with a unique key + shard key on those columns. In 7.5 default_table_type is set to columnstore by default for new installs (it will remain as rowstore on upgrades).

-Adam