When I work with MySQL databases for example, I get integrity restriction by declaring:
SET FOREIGN_KEY_CHECKS = 1;
Foreign keys, for example
CONSTRAINT Key_DimProduct FOREIGN KEY (Key_DimProduct) REFERENCES dw.dim_product (key_dim_product) ON DELETE RESTRICT ON UPDATE RESTRICT
I could not find a way to declare foreign keys. In order to test the consistency of the data, I created a DW and in one of the dimension tables I was able to remove a parent record to which it refers to a child record in the fact table. How could I protect these relationships directly in the MemSQL DBMS?
Unfortunately MemSQL does not currently support foreign keys. (Unsupported MySQL Features · SingleStore Documentation). If you try to declare a foreign key, you’ll get an error/warning about that. You will probably need to look into enforcing the referential integrity on the application side.
So we can say that memsql is not completely ACID because it does not have a strong consistency?
I ask this because I am conducting a research and it is important to confirm.
What do you mean by “strong consistency”? As I understand it (Strong consistency - Wikipedia), it is a separate concept from referential integrity. MemSQL is ACID. MemSQL’s isolation level is read_committed, not serializable.
@alesanco it is the application’s job to ensure that a transaction is “consistent” – the C part of ACID. This means that the transaction moves the database from one semantically consistent state to another, with respect to the real-world interpretation of the information in the database. Referential integrity is just one of many possible kinds of semantic consistency in the data. Referential integrity could be enforced by the application. We don’t support it automatically right now.
Other semantic constraints that might have to be guaranteed by the application are things like: you can’t have a negative bank balance, you must have at list one line item in a supermarket checkout transaction, etc.
How can you enforce referential integrity without serializable? For example, a delete on a parent would require checking that there are no child rows. In read committed, a concurrent insert may succeed, leaving an orphan row. Avoiding this in read commited requires locking the parent row or whole child table - this doesn’t scale. Is there another solution?