If you are using SQL Server 2008 a Index filter would maybe your solution: http://msdn.microsoft.com/en-us/library/ms188783.aspx This is how I enforce a Unique Index with multiple NULL values CREATE UNIQUE INDEX [IDX_Blah] ON [tblBlah] ([MyCol]) WHERE [MyCol] IS NOT NULL
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
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
The CHECK is being executed after the row has been inserted, so the range overlaps with itself. You’ll need to amend your WHERE to include something like: @MyTableId <> MyTableId. BTW, your WHERE expression can be simplified. Ranges don’t overlap if: end of the one range is before the start of the other or start … Read more
A check constraint follows the same syntax rules as conditions for a WHERE clause: alter table foo add constraint check_email check (REGEXP_LIKE(email,’your_regex_goes_here’,’I’)); More details in the manual: for Oracle 11 – http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF52141 for Oracle 12 – https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141 Edit: There are however some restrictions on what you can actually use in a check constraint: Oracle 11 … 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
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
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