Use of foreign keys in a DB - are they necessary?

Hi All
Im starting to design a complex system using cubesql.
I’m considering should I use foreign keys, which I see the purpose is to reinforce business rules on the database.

Since I never actually delete data, just mark it as archived, the rules would never be triggered.

In the past I have simply used the knowledge of the relationship between fields in the past, and avoided FK’s.

Do foreign keys applied as trigger add extra overhead in a DB?

Am I adding future problems by not using foreign keys?

Thanks in advance, very much appreciate help or any thoughts on this.

Foreign keys are not necessary but they help ensure data integrity.

And they’re not just for deleting. They also can prevent you from adding invalid data to your DB.

There’s no reason to use triggers to enforce foreign keys as support is built in to SQLite.

Foreign keys are a one-time setup when you create the DB schema so it’s not a big difficulty to use them. The only thing is you have to manually enable them (so that they are actually enforced) each time to connect to SQLite.

https://documentation.xojo.com/index.php/SQLiteDatabase#Foreign_Keys

Thanks Paul.
I didn’t realise that about sqlite.
Nice and easy for Xojo to enable as well.

Cheers

Foreign keys, check constraints, unique constraints, candidate keys etc are essentially if you are designing a complex database, they go to the very core of maintaining the integrity of the database. Thousands of hours have been put into designing and optimizing those features - you will not find a more efficient way of maintaining the database integrity. So put the time into learning how to use them.

On the other hand avoid using triggers like the plague when it comes to database integrity. They are not optimized for a start and as you start to add complex tasks to the application, they will usually start firing when you don’t need them. Of course the next step is to turn them off and later forget to turn them back on! And then you really have a messy situation.

Thanks for your help James .
Looking into it now
Cheers