How to create foreign key in Cassandra CQL

Question

I'm trying to replicate a SQL database in Cassandra, but, while I had no problem creating the tables, I found that I cannot find an example easy to understand that 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 the Cassndra CQL code for the same purpose?

Aldana

You're thinking in SQL and translating to noSQL. To master noSQL, think in noSQL. That is, the paradigms are different, and forcing SQL patterns into the noSQL world will only lead to frustration. – Indivertible

Okay. Well, just trying to help you in your transition to nosql databases. And even though you have your answer, keep in mind that SO is about preserving information for others that come along, and comments can help them, too. – Indivertible

Thanks for that. Sorry if I where rude earlier ;) – Aldana

Answer 1

Simple answer is: There is no CQL code for the same purpose.

CQL does not have a concept of foreign keys or any concept of constraints between tables in the same way that you can't do joins between tables.

If you need a constraint between tables then you would need to handle this in code.

Kierkegaard

So, the only thing I can do is control them via code? Can't I somehow prepare the database to do something similar? (just curious this time) – Aldana

@Aldana Cassandra and other non-relational databases don't have anything like this concept, because you should be modeling your tables to suit your queries (as-in, one table for each expected query). With that type of approach, there is no need for foreign keys. – Historied

Answer 2

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

this seems a bit misleading. there are consistency risks with fully denormalizing in Cassandra... best way to insure against consistency issues is doing statements in batches with an all or nothing commit using the apply batch command

Wherewithal

Answer 3

from django.db import models

# Create your models here.

import uuid
from cassandra.cqlengine import columns
from django_cassandra_engine.models import DjangoCassandraModel
class TestModel(DjangoCassandraModel):
    example_id=columns.UUID(primary_key=True, default=uuid.uuid4)
    json_data =columns.Text(required=False)

Quartersaw

from django.db import models # Create your models here. import uuid from cassandra.cqlengine import columns from django_cassandra_engine.models import DjangoCassandraModel class TestModel(DjangoCassandraModel): example_id=columns.UUID(primary_key=True, default=uuid.uuid4) json_data =columns.Text(required=False) – Quartersaw