RDBMS constraints are oversold usually. For instance it is usually the case that things like foreign-key constraints are avoided in production because they cause performance and maintenance nightmares.

Widely-used constraints like UNIQUE are production-quality in SQLite.