Tagged with "consistency"
Fun MySQL fact of the day:
On Friday, I left you with a cliff-hanger suggesting that foregoing the use of foreign key constraints can result in undetectable account/identity takeovers, data leakage, data loss, corruption, and so on, and so on. And it can. And not only by accident, but also by a really fun MySQL ... read moreFun MySQL fact of the day: foreign keys are important
A Foreign Key is a table constraint that allows a database index to remain consistent based on the existence (or non-existence) of another row in a another table. For example, MySQL will prevent the removal of a referenced row until all other referencing rows have first been removed. It will ... read more
Fun MySQL fact of the day: REPLACE your expectations
So, if we agree that we should avoid using INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE, surely we can use REPLACE INTO to upsert a row, right?! And yes, you can, if you want a DoS
Fun MySQL fact of the day: IGNORE at your own peril
Yesterday, we saw how INSERT ... ON DUPLICATE KEY UPDATE can unknowingly result in not-completely-expected situations. To get around that, you may have thought to use INSERT IGNORE, instead. Often, INSERT IGNORE is used when attempting to insert a row that may already exist. The underlying expectation a ... read more
Fun MySQL fact of the day: a CHECKered past
A Check Constraint is a table constraint, described in SQL-89, that allows a database index to remain consistent based on a specified search criteria or expression. For example, we could ADD CONSTRAINT is_even_ck CHECK(my_int_col % 2 = 0) to ensure that the value of the my_int_col column in every row ... read more
Fun MySQL fact of the day: inconsistent consistency
In being consistency inconsistent, theC in ACID and the C in CAP both stand for Consistency, but don't describe the same properties. In fact, the C in CAP actually stands for Atomic Consistency (or Linearizability), and, oh holy crap!, the Atomic part of Atomic Consistency isn't the ... read more
Fun MySQL fact of the day: chaos without ORDER BY
ANSI SQL-92 states that the order of a result set in the absence of an ORDER BY is implementation specific. This means that the same query without an ORDER BY clause run n times may return rows in n different orders. ... read more