Constraints are part of a database schema definition. A constraint is usually associated with a table and is created with a CREATE CONSTRAINT or CREATE ASSERTION SQL statement. They define certain properties that data in a database must comply with. They can apply to a column, a whole table, more than one table or an … Read more
While it is true that there is no DROP ALL TABLES command you can use the following set of commands. Note: These commands have the potential to corrupt your database, so make sure you have a backup PRAGMA writable_schema = 1; delete from sqlite_master where type in (‘table’, ‘index’, ‘trigger’); PRAGMA writable_schema = 0; you … Read more
Right from the manual: The types decimal and numeric are equivalent. Both types are part of the SQL standard. As for the “why do I need to use it”, this is also explained in the manual: The type numeric can store numbers with a very large number of digits and perform calculations exactly (Emphasis mine). … Read more
For more international use, one schema to consider is the one used by Drupal Address Field. It’s based on the xNAL standard, and seems to cover most international cases. A bit of digging into that module will reveal some nice pearls for interpreting and validating addresses internationally. It also has a nice set of administrative … Read more
Take a look at the CHECKSUM command: SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK); That will return the same number each time it’s run as long as the table contents haven’t changed. See my post on this for more information: CHECKSUM Here’s how I used it to rebuild cache dependencies when tables changed: ASP.NET 1.1 database … Read more
Not all data is relational. For those situations, NoSQL can be helpful. With that said, NoSQL stands for “Not Only SQL”. It’s not intended to knock SQL or supplant it. SQL has several very big advantages: Strong mathematical basis. Declarative syntax. A well-known language in Structured Query Language (SQL). Those haven’t gone away. It’s a … Read more
Summary of what I’ve seen so far: Some people don’t like cascading at all. Cascade Delete Cascade Delete may make sense when the semantics of the relationship can involve an exclusive “is part of” description. For example, an OrderLine record is part of its parent order, and OrderLines will never be shared between multiple orders. … Read more
There is nothing like a silver bullet, everything is built to solve specific problems and has its own pros and cons. It is up to you, what problem statement you have and what is the best fitting solution for that problem. I will try to answer your questions one by one in the same order … Read more
There’s a book in Martin Fowler’s Signature Series called Refactoring Databases. That provides a list of techniques for refactoring databases. I can’t say I’ve heard a list of database patterns so much. I would also highly recommend David C. Hay’s Data Model Patterns and the follow up A Metadata Map which builds on the first … Read more