Ask HN: What's your preferred way of ensuring complex database integrity?

What I mean really is: when working with relational databases, and you have complex constraints to ensure (not trivial things like notnull but something like: "two tables away" uniqueness, etc..), do you like filling it with tricks using PKs/FKs/UNIQUEs to ensure integrity or do you prefer writing some code that will get executed as you application boots up, that ensures that all the more complex requirements are met?