How to create a foreign key in Cassandra CQL

Question

I am trying to replicate an SQL database in Cassandra, but although I had no problems creating tables, I found that I could not find an example that is easy to understand, which shows how I can create foreign keys in Cassandra.

So, if I have this in SQL:

CREATE TABLE COOP_USUARIO ( CI VARCHAR2 (13 BYTE) NOT NULL , CUENTA VARCHAR2 (20 BYTE) NOT NULL , NOMBRE VARCHAR2 (50 BYTE) NOT NULL , EMAIL VARCHAR2 (255 BYTE) NOT NULL , DIRECCION VARCHAR2 (255 BYTE) , CIUDAD NUMBER NOT NULL , TELEFONO VARCHAR2 (10 BYTE) NOT NULL , TIPO_PERSONA NUMBER (1) NOT NULL , ); CREATE UNIQUE INDEX COOP_USUARIO_PK ON COOP_USUARIO( CI ASC ); ALTER TABLE COOP_USUARIO ADD CONSTRAINT COOP_USUARIO_PK PRIMARY KEY ( CI ) ; CREATE TABLE COOP_CIUDADES ( ID NUMBER NOT NULL , NOMBRE VARCHAR2 (25 BYTE) NOT NULL , PROVINCIA NUMBER NOT NULL ) ; CREATE UNIQUE INDEX COOP_CIUDADES_PK ON COOP_CIUDADES ( ID ASC ); ALTER TABLE COOP_CIUDADES ADD CONSTRAINT COOP_CIUDADES_PK PRIMARY KEY ( ID ) ; ALTER TABLE COOP_USUARIO ADD CONSTRAINT COOP_USUARIO_CIUDADES_FK FOREIGN KEY ( CIUDAD ) REFERENCES COOP_CIUDADES ( ID ) ; 

What is Cassndra CQL code for the same purpose?

Answer 1

The simple answer is: CQL code does not exist for the same purpose.

CQL does not have the concept of foreign keys or any concept of constraints between tables in the same way that you cannot make joins between tables.

If you need a constraint between tables, you will need to handle this in code.

Answer 2

"one table for each expected query.) With this type of approach, there is no need to use a foreign key

this seems a bit wrong. there are risks of consistency with full denormalization in Kassandra ... the best way to insure yourself against problems with consistency is to make statements in batches with everyone or nothing using the apply batch command