23

String handling in TSQL is poor. PATINDEX supports a limited set of wild cards but has no support for character classes such as \s to match white space or quantifiers. It isn't impossible to do the validation for this relatively simple regex with a native TSQL expression. The max length of 12 is enforced by the datatype and use of varchar means there are ...


22

So you look up other tables in a CHECK constraint. CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction in the manual: Currently, CHECK expressions cannot contain subqueries nor refer ...


19

You need to use a check constraint: create table kevin ( one integer, other integer, constraint only_one_value check ( (one is null or other is null) and not (one is null and other is null) ) ); This ensures that at least one of the columns has a value and that not both have a value. If those are varchar columns ...


13

My wild guess: "more efficient" means "less time is required to perform the check" (time advantage). It may also mean "less memory is required to perform the check" (space advantage). It might also mean "has less side effects" (such as not locking something or locking it for shorter periods of time)... but I don't have a way to know or check that "extra ...


11

You can use a filtered unique index for this: CREATE UNIQUE INDEX LinkedID_Unique_except_Zeroes_or_Null ON dbo.oldAccessTable (LinkedID) WHERE LinkedID <> '00000000' ; The condition (LinkedID <> '00000000') takes care of both requirements, i.e. rows with '00000000' or NULL are ignored and not stored in the index, so they are not ...


10

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value: ( (("qb_id" IS NOT NULL) :: INTEGER + ("xero_id" IS NOT NULL) :: INTEGER + ...


9

Just add the constraint as NOT VALID From the manual: If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified ...


9

The check constraint is applied after. You can see this from the fact that the following succeeds. DECLARE @T TABLE(X INT DEFAULT 1 CHECK (X = 1)); INSERT @T DEFAULT VALUES; Also the execution plan shows that the assert operator operates on the values output from the table insert operator so is checking the values that were actually inserted.


8

Canadian postcodes are in the format A1A 1A1, where A is a letter and 1 is a digit, with a space separating the third and fourth characters ... Postal codes do not include the letters D, F, I, O, Q or U, and the first position also does not make use of the letters W or Z. So the following should do it. CHECK (PostCode LIKE REPLACE(REPLACE('Alpha1[...


8

Use a CHECK constraint. CHECK constraints do not fail when the value is null. If the value is null, the condition of the check constraints usually evaluates to UNKNOWN and the row is accepted. Only when the condition evaluates to FALSE, the row is rejected. Another option is to use a scalar UDF inside the check constraint. No, no, no. User defined ...


8

Assuming the digits to the left of the + are measured in units of 100, you could perhaps create constraints like this: CREATE TABLE dbo.SurveyData ( StartPostStation int NOT NULL , StartPostPlus decimal(38,3) NOT NULL , EndPostStation int NOT NULL , EndPostPlus decimal(38,3) NOT NULL , CONSTRAINT StartMin CHECK ((StartPostStation ...


8

There is no difference in SQL Server between those two statements. Each results in NOT NULL column (not a constraint) with a single check constraint. The only difference is that second one creates a system-named check constraint, something like 'CK__t__ProjNum__4AB81AF0'.


8

It seems the database and column collations are case-insensitive so the LIKE expression is also case-insensitive. One way to perform a case-sensitive compare in this scenario is by adding a COLLATE clause, specifying a case-sensitive collation. For example, if your database default collation is a case insensitive collation such as Latin1_General_CI_AS, the ...


7

A foreign key can not be "conditional". The only "exception" to that rule are null values which can't reference another table by definition. If I understand your question correctly, you are trying to implement a constraint that says "if refunded is true then refund_id must reference an existing transaction". I don't think you need the refunded column at ...


7

The syntax you are using is for a column-level constraint, so it can only apply to the column it is attached to... try naming your constraints at the table level instead (this also allows encourages you to give your constraints meaningful names instead of the terrible system defaults): CREATE TABLE dbo.Medicaments -- always use schema* ( MedicamentID INT ...


7

Looking up a value from another table would require a subquery, but the documentation says: The expression of a CHECK constraint may not contain a subquery. There is no such restriction on a trigger's WHERE clause: CREATE TRIGGER employee_salary_check BEFORE INSERT ON employee WHEN NEW.salary < (SELECT min FROM position WHERE id_position = NEW....


7

The crucial point you may not be aware of, quoting the manual: It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Bold emphasis mine. Using cardinality() fixes this case like a_horse explains. Explicitly disallowing empty arrays would do the same, cheaper: CHECK (words <> '{}') ...


6

You cannot have a Primary Key that only works for some of the rows. It would really cease being a Primary key at that point. Emphasis on Primary. You can play with filtered indexes for a Unique index and that filter can be on values you specify. But a primary key is there to enforce uniqueness so you can't make only some of the table part of the key. You ...


6

There seems to be some confusion. The CHECK (Id > 1000) is not a PRIMARY KEY constraint, it is a CHECK constraint. You can have a partial (filtered*) unique index, if that's what you are after: UNIQUE (id) WHERE (id > 1000) (but this is not a primary key constraint or an index that can be used for the primary key constraint). Create it with: CREATE ...


6

Yes, use check constraints Yes, you should add check constraints in your database to ensure valid values, as well as checking in your app. The database side makes for a double-check in case your app’s code has a hole or bug. Also, you may use other apps such as db admin tools to interact with your data, so it is good to have the database enforce the rules. ...


5

Check constraints can only be used to verify if the data falls within a given set of parameters. The CHECK(...) clause is essentially validated as a boolean condition; if the result is 1, the row is inserted, if the result is 0 the row is not inserted, and an error is returned to the client. To test this, I created your table, along with your proposed ...


5

Mark all your existing rows as old: ALTER TABLE integrations.billables ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false; UPDATE integrations.billables SET is_old = true; And set up the constraint to ignore old rows: ALTER TABLE integrations.billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK ( NOT(("qb_id", "xero_id", "...


5

You have NULLs that violate the CHECK CONSTRAINT. To verify that this is indeed the problem, run this SQL. SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL OR xero_settings IS NOT NULL OR... (fill in the fields that correspond to those in the CONSTRAINT). This will give you all the records that have fields which have a NULL ...


5

You can negate your constraint to find out the rows that does not satisfy it: SELECT * FROM integrations.accounts WHERE NOT ((("qb_settings" IS NOT NULL) or ("xero_settings" IS NOT NULL) or ("freshbooks_settings" IS NOT NULL) or ("myob_settings" IS NOT NULL) or ("ppy_settings" IS NOT NULL))) This can be ...


5

The LIKE operator in SQL does not work like regular expressions. The pattern matches allowed by this operator are very simple, compared to the powerful regexp. Actually, you have two metacharacters: % means (0 or more of anything), which would be the equivalent of a .* in a regexp, and _ meaning (1 of anything), which would be the equivalentof . in a regexp. ...


5

array_length(words, 1) will return null if the specified array dimension does not exists. You should use cardinality() instead: create table words_table ( id serial primary key, words varchar(20)[] CHECK (cardinality(words) > 0) ); insert into words_table (words) values ('{}'); results in: ERROR: new row for relation "words_table" violates ...


5

Check constraints are not for enforcing uniqueness. Unique function-based index can do the job (because Oracle accepts multiple NULL values on unique columns). create table test1(ID_PROCESS int , PERIOD int, CUSTOMER int, STATUS varchar2(20)); create unique index idxu1_test1 on test1 (case when STATUS ='COMPLETED' then PERIOD else null end, ...


5

CHECK (NOT (flag_afil = 1 AND fec_renum IS NULL))


4

This was not possible in Postgres up to version 9.1. From 9.2 onwards you can define a check constraint as NOT VALID (equivalent to WITH NOCHECK in MS SQL Server). See http://www.postgresql.org/docs/9.2/static/sql-altertable.html for more detail. I'm not generally happy with this sort of thing where it is at all possible to avoid. A compromise if you have a ...


4

Your CHECK constraint can be much simpler: ALTER TABLE billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK (qb_id IS NOT NULL OR xero_id IS NOT NULL OR freshbooks_id IS NOT NULL OR unleashed_id IS NOT NULL OR csv_data IS NOT NULL OR myob_id IS NOT NULL) NOT VALID; Or even ...


Only top voted, non community-wiki answers of a minimum length are eligible