One common way is to add a classifier that is "inherited" like:
CREATE TABLE products
( product_id ... NOT NULL PRIMARY KEY
, product_type ... NOT NULL
, UNIQUE (product_id, products_type)
, CHECK (product_type IN ('food', 'furniture'))
product_type would typically be a code of some kind. Might be a foreign key to a "lookup&...
There can be several reasons that cause Database Logical Consistency based I/O error:
Unexpected system shutdown/crash or forced shutdown
SQL administrator tries to query or modify SQL data
This article on SQL Server Central about logical consistency based error in SQL is amazingly written by SQL DBA.
Transaction atomicity implies consistency
This is not true. A transaction that adds an order for a nonexistent customer is atomic, but makes data inconsistent.
To follow your example, a consistent bank transaction must guarantee that the sum of all debits and credits is exactly 0. An atomic transaction must only guarantee that either all necessary tables, ...
What you are proposing is not really CAP Theorem unless the cluster is totally a share-nothing architecture. Please note what CAP Theorem is from my old post from Jan 01, 2013 : Consistency in ACID and CAP theorem, are they the same?
Consistency (All Nodes Have Same Data via Eventual Consistency)
Partition-Tolerance : system continues to ...
You have two unique constraints, and you say that the one on shortname causes an error only rarely.
INSERT ... ON CONFLICT ON (shorturl) DO NOTHING;
Then check if you got an error and how many rows were inserted.
if you got an error, prompt the user for a new shortname
if no row was inserted, generate a new shorturl
Then you will only get error ...
Is it possible to do this in multiple queries but still realize the same level of consistency as having performed these individual selects as a single query?
It depends on the RDBMS.
In SQL Server you can use a transaction with SNAPSHOT transaction isolation level to get a consistent view of the database across multiple queries.
The isolation level Oracle ...
For SQL Server:
But what's about single statements?
In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that ...
Maybe I'm simplifying the requirement, but to satisfy the requirement and your concern is to simply set the required columns in the subtype to NOT NULL. When a new PRODUCT row of food is created, a new FOOD row will also be created, and if no info is provided required for FOOD, then the database will reject the transaction.