MemSQL-在创建表的同时将代理键作为主键和不同的唯一键

问题描述

我遇到这样的情况，我需要一个替代键(id)来代替复合键(4个字段组合起来是唯一的:project_id, dataset_id, table_id, view_name)，以便在其他表中轻松引用它.

I have a situation that I need to have a surrogate key (id) in place of a composite key (4 field combined to be unique: project_id, dataset_id, table_id, view_name) to easily refer that in other tables.

因此，为此，我将id字段用作主键，并将上面提到的其他4个字段用作唯一键.这在MySQL中是允许的，但在MemSQL中是不允许的.

So to do this I used id field as Primary key and other 4 fields mentioned above as unique keys. This is allowed in MySQL but not in MemSQL.

Error Code: 1895. The unique key named: 'project_id' must contain all columns specified in the primary key when no shard key is declared

所以我添加了id字段作为分片键，但是没有用.

So I added the id field as the Shard key but no use.

CREATE TABLE `table_access_details` (
  `id` integer primary key,
  `project_id` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `dataset_id` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `table_id` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `view_name` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL ,
  `upload_id` decimal (14,0) DEFAULT NULL,
  `modified_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  #SHARD KEY (`id`),
  unique(`project_id`,`dataset_id`,`table_id`,`view_name`)
);

如何克服MemSQL中的这种情况?

How can I overcome this situation in MemSQL?

推荐答案

因此，您想要唯一键(id)还是唯一键(project_id，dataset_id，table_id，view_name)?这在memsql的分片表中是不可能的-唯一键不能在各个分片之间有效实施.您的选择是:不要同时使用唯一键，也不要将表用作参考表.

So, you want unique key (id) as well as unique key (project_id, dataset_id, table_id, view_name)? This is not possible in a sharded table in memsql - the unique key cannot be efficiently enforced across shards. Your options are: don't use both unique keys, or make the table a reference table.

这篇关于MemSQL-在创建表的同时将代理键作为主键和不同的唯一键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！