4 ms·

Postgres constraints for newbies

paulryanrogers 5y ago
DB constraints can be a hindrance if the business rules are in flux, and you already have them in the app layer. That said I did build a relatively mature system using a lot of DB constraints, even using magic prefix in error messages and constraint names to bubble up into the UI. Nowadays I stick to only FKs, unique, and size parameters.
- Osiris 5y ago
  I agree. It's not wise to encode business rules into the database. However, there are certain kinds of constraints that are still valid, specifically if they are data structure constraints (like a value must be >= 0).
  - fitblipper 5y ago
    This has been a continual discussion between me and my coworkers. Do you know of any reading we can look at to better understand the tradeoffs of when it is appropriate to put constraints on the data?
- KwisaksHaderach 5y ago
  The article is about the basic constraints you mentioned, FKs, PK/unique, check constraints. I agree with keeping the database as dumb as possible with only those, heck even dropping those (except for PKs) at the beginning gives you more agility.
- nerdyadventurer 5y ago
  > DB constraints can be a hindrance if the business rules are in flux, and you already have them in the app layer What you here mean as "DB Constraints" if not FK and etc?
  - paulryanrogers 5y ago
    Check constraints can compare new values against bounds different than the column's type definition. Like maintaining a SMALLINT between 1-100. Or one could even write trigger based checks that ensure integrity among multiple records' values. (Though I don't recommend it unless the constraints are unlikely to change or one has little control over app layer validation.)
ewuhic 5y ago
Like your articles, crunchydata, an article about proper Postgres replication and backup schedule for production from the viewpoint of a solutions architect would be highly appreciated.
prometheon1 5y ago
Interesting read! I'm not very familiar with constraints. > We can set up these first two tables for users and rooms without any constraints Based on this sentence I wondered what the difference is between a primary key and a constraint. I looked up primary key in the postgres docs, it seems that PRIMARY KEY is also a constraint? > Adding a foreign key constraint here is a really easy way to tie our reservations table to the other data tables ensuring they are always tied together with primary keys. This did not make me understand what the foreign key actually does. The first sentence of the pg docs on foreign keys helped a bit: A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. > You can also create the foreign key constraints after the fact on an existing table via ALTER TABLE like this: Now I was wondering what happens when you use ALTER TABLE to add a foreign key to a table that already has data. Apparently in mysql it will only succeed if the existing data matches the references data[0], I assume this is the same in postgres. Finally I'm wondering whether I could join tables automatically using the foreign keys in a select statement instead of typing all the column names, apparently not :( [0] https://stackoverflow.com/a/19550474 https://stackoverflow.com/a/19550474

DB constraints can be a hindrance if the business rules are in flux, and you already have them in the app layer. That said I did build a relatively mature system using a lot of DB constraints, even using magic prefix in error messages and constraint names to bubble up into the UI. Nowadays I stick to only FKs, unique, and size parameters.

Osiris 5y ago
I agree. It's not wise to encode business rules into the database. However, there are certain kinds of constraints that are still valid, specifically if they are data structure constraints (like a value must be >= 0).
- fitblipper 5y ago
  This has been a continual discussion between me and my coworkers. Do you know of any reading we can look at to better understand the tradeoffs of when it is appropriate to put constraints on the data?
KwisaksHaderach 5y ago
The article is about the basic constraints you mentioned, FKs, PK/unique, check constraints. I agree with keeping the database as dumb as possible with only those, heck even dropping those (except for PKs) at the beginning gives you more agility.
nerdyadventurer 5y ago
> DB constraints can be a hindrance if the business rules are in flux, and you already have them in the app layer What you here mean as "DB Constraints" if not FK and etc?
- paulryanrogers 5y ago
  Check constraints can compare new values against bounds different than the column's type definition. Like maintaining a SMALLINT between 1-100. Or one could even write trigger based checks that ensure integrity among multiple records' values. (Though I don't recommend it unless the constraints are unlikely to change or one has little control over app layer validation.)

ewuhic 5y ago

Like your articles, crunchydata, an article about proper Postgres replication and backup schedule for production from the viewpoint of a solutions architect would be highly appreciated.

prometheon1 5y ago

Interesting read! I'm not very familiar with constraints. > We can set up these first two tables for users and rooms without any constraints Based on this sentence I wondered what the difference is between a primary key and a constraint. I looked up primary key in the postgres docs, it seems that PRIMARY KEY is also a constraint? > Adding a foreign key constraint here is a really easy way to tie our reservations table to the other data tables ensuring they are always tied together with primary keys. This did not make me understand what the foreign key actually does. The first sentence of the pg docs on foreign keys helped a bit: A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. > You can also create the foreign key constraints after the fact on an existing table via ALTER TABLE like this: Now I was wondering what happens when you use ALTER TABLE to add a foreign key to a table that already has data. Apparently in mysql it will only succeed if the existing data matches the references data[0], I assume this is the same in postgres. Finally I'm wondering whether I could join tables automatically using the foreign keys in a select statement instead of typing all the column names, apparently not :( [0] https://stackoverflow.com/a/19550474 https://stackoverflow.com/a/19550474