The best place to enforce data integrity is the database. Rest assured that some developer, intentionally or not, will find a way to sneak inconsistent stuff into the database if you let them! Here’s an example with check constraints: CREATE FUNCTION dbo.SignupMismatches() RETURNS int AS BEGIN RETURN ( SELECT count(*) FROM Shift s LEFT JOIN … Read more
Note, what you really want is a foreign key constraint. That said, to get a “query” into a check you can write a function that contains the query and outputs a scalar value, then use that function in the check constraint. CREATE FUNCTION myFunction ( @field DATATYPE(?) ) RETURNS VARCHAR(5) AS BEGIN IF EXISTS (SELECT* … Read more
CHECK constraints are not supported by MySQL. You can define them, but they do nothing (as of MySQL 5.7). From the manual: The CHECK clause is parsed but ignored by all storage engines. The workaround is to create triggers, but they aren’t the easiest thing to work with. If you want an open-source RDBMS that … Read more
I would not put constraints like these in the save method, it’s too late. Raising an exception there, doesn’t help the user who entered the data in the wrong way, because it will end up as a 500 and the user won’t get the form with errors back etc. You should really check for this … Read more
It is not supported to look beyond the current row in a CHECK constraint. http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html says: A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint can reference multiple columns. Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns … Read more
MySQL 8.0.16 is the first version that supports CHECK constraints. Read https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html If you use MySQL 8.0.15 or earlier, the MySQL Reference Manual says: The CHECK clause is parsed but ignored by all storage engines. Try a trigger… mysql> delimiter // mysql> CREATE TRIGGER trig_sd_check BEFORE INSERT ON Customer -> FOR EACH ROW -> BEGIN … Read more
The first syntax is redundant – the WITH CHECK is default for new constraints, and the constraint is turned on by default as well. This syntax is generated by the SQL management studio when generating sql scripts — I’m assuming it’s some sort of extra redundancy, possibly to ensure the constraint is enabled even if … Read more