Feature 'FOREIGN (non-SHARD) key to a sharded table' is not supported by SingleStore

user_8827408 0
Viewed 20

I try to create a table with a foreign key to a reference table in SingleStore MemSQL:

-- Create table
create rowstore reference table DEPT
(
 deptno INT(2) not null,
 dname VARCHAR(14),
 loc VARCHAR(13),
 constraint PK_DEPT primary key (DEPTNO)
); 

create rowstore table EMP
(
 empno INT(4) not null,
 ename VARCHAR(10),
 job  VARCHAR(9),
 mgr  INT(4),
 hiredate DATE,
 sal  decimal(7,2),
 comm  decimal(7,2),
 deptno INT(2),
 constraint PK_EMP primary key (EMPNO)
 ,constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO)
);

but this fails with:

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature 'FOREIGN (non-SHARD) key to a sharded table' is not supported by SingleStore.

The message says that the opposite is not possible (foreign key to a shared table) but here I try to create a foreign key from a sharded table to a reference table. This should be possible even when distributed transactions are not supported because it is a single-shard verification.

singlestore
1 Answers
user_194163760
Related