In my experience, many of the projects I have read in the past didn't have relationship definitions in the database, instead they only defined them in the source code. So I'm wondering what are the advantages/disadvantages of defining relations between tables in the database and in source code? And the broader question is about other advanced features in modern databases like cascade, triggers, procedures... There are some points in my thoughts:
In the database:
Correct data from design. Prevent application errors which can cause invalid data.
Reduce network round trip to application when inserting/updating data as application has to make more query(s) to check data integrity.
In source code:
More flexible.
Better when scaling to multiple databases, as sometimes the relation can be cross-database.
More control over data integrity. The database doesn't have to check every time the application modifies data (complexity can be O(n) or O(n log n) (?)). Instead, it's delegated to application. And I think handling data integrity in the application will lead to more verbose error messages than using the database. Eg: when you create an API server, if you define the relations in the database, and something goes wrong (like the referenced entity doesn't exist), you will get an SQL Exception with a message. The simple way will be to return 500 to the client that there is an "Internal server error" and the client will have no idea what is going wrong. Or the server can parse the message to figure out what's wrong, which is an ugly, error-prone way in my opinion. If you let the application handle this, the server can generate a more meaningful message to client.
Is there anything else?
Edit: as Kilian points out, my point about performance & data integrity is very misguided. So I edited to correct my point there. I totally understand that letting the database handle it will be a more efficient and robust approach. Please check the updated question and give some thoughts about it.
Edit: thank you everyone. The answers I received all point out that the constraints/relations should be defined in the database. :). I have one more question, as it is quite out of scope of this question, I've just posted it as a separate question: Handle database error for API server. Please leave some insights.