Does yugabyte ensure foreign key constraint?

General
1

In yugabyte, Can table1 have foreign key constraint with primary key of table 2?

Does yugabyte provide referential integrity constraint?

2

Yes sure. In YSQL, with same same syntax as PostgreSQL:

3

@FranckPachot I don’t get this.

We are using the yugabyte database in production, but with client driver ycql(instead of ysql)

Coming from MySQL & MsSql backgorund, just wondering,
How does a change in client driver(YSQL instead of YCQL) change the behavior of database schema? that can ensure foreign key constraints, with same yugabyte DB, just by changing the client driver…

Can’t we introduce foreign key constraints using YCQL client driver?

4

Ok, so no you cannot use foreign keys on tables created with the YCQL API. But you can create tables with the YSQL API and then have foreign key. The two APIs share the same database, but isolated from eachother.

If you want to use relational tables, referential integrity, transactions… like in a SQL database then you should use the YSQL API. The YCQL API is for cassandra-like microservices.

Currently, you cannot read YCQL from YSQL so you need an external tool to transfer data from one to the other, for example. We will enable access through the Forign Data Wrapper in a future release: [YSQL] Access YCQL tables via the YSQL API · Issue #830 · yugabyte/yugabyte-db · GitHub
There’s is more info about the API isolation: FAQs about YugabyteDB API compatibility | YugabyteDB Docs

5

Clarification: YSQL and YCQL are much more than just APIs in a usual sense, they are entirely different query layers.
While they both are backed by the same low-level DocDB (responsible for storage, replication, etc.), syntax, functionality, behavior, performance would all differ drastically.
As such, there’s no interop possible between YSQL and YCQL.

6

@alex

So that means,

  1. Using ysql, can we introduce database triggers and stored procedures, unlike ycql?

  2. Using ycql, with begin transaction and end transaction, does yugabyte database ensure, write consistency, for the queries that modify more than one table ?

  3. does multi sharding of a table happens using ycql? But not ysql…

  4. Is partition key concept visible only through ycql but not ysql?

7
  1. Yes, (almost) everything supported by PostgreSQL 11 is supported by Yugabyte YSQL.
  2. If the tables are transactional in YCQL, we do ensure write consistency as per snapshot isolation definition - Transaction isolation levels | YugabyteDB Docs. Sorry, I do not know which exact guarantees are provided for non-transactional tables - cc @FranckPachot.
  3. YSQL supports both hash and range sharding, YCQL only supports hash sharding - see Hash and range sharding | YugabyteDB Docs
  4. Unlike YCQL, right now you won’t be able to see each tablet’s partition key bounds through web GUI when using YSQL. This is a know issue which we plan to address sooner rather than later - again, if that is what you ask.