Stack Overflow
Stack Overflow

Questions tagged [check-constraints]

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

SQL CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range. The logical expression would be the following: salary >= 15000 AND salary <= 100000.

624 questions
3
votes
1 answer

Regex CHECK constraint not working with SQL server

im trying to reject all inputs not in the format "03 xxxx xxxx" so i created a table like create table records ( .... num varchar(255) NOT NULL, ... CONSTRAINT num_check CHECK (num like '03 [0-9]{4} [0-9]{4}') ) which should (i think?) accept for…
TrewTzu
  • 1,110
  • 2
  • 11
  • 27
3
votes
1 answer

Parentheses Issue in Check Constraint

I'm trying to create the following check constraint. ( [Href] IS NOT NULL AND [ETag] IS NOT NULL AND [UserName] IS NOT NULL AND [Password] IS NOT NULL AND [UID] IS NOT NULL ) OR ( [Href] IS NULL AND [ETag] IS NULL AND …
Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
2
votes
2 answers

How do I create a DOMAIN that accept NULL?

I can't seem to figure out how to get a custom domain to accept a NULL value. Notice, for shits and giggles I've tried it many different ways: DROP SCHEMA census CASCADE; CREATE SCHEMA census; -- FIRST FOUR METHODS: NULL before CHECK, in CHECK, and…
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
2
votes
3 answers

TSQL - How to check a value against a value in a different column before inserting?

Is there a way to implement a CHECK constraint that checks a value against another value in a different column in the same table? Specifically, I want to ensure that a "checkout" date value to be inserted is greater than the "checkin" date value in…
user99721
2
votes
1 answer

How to use check constraints in mysql table?

The values of produce_prices must be < wholesaler_prices must be < retailer_prices. Can I apply check constraints for these to happen? The table is the following: CREATE TABLE `crop_products_price` ( `ID` int(11) NOT NULL, `office_type_id`…
Tesfaye
  • 21
  • 1
2
votes
1 answer

How to define CHECK constraint for a Postgres DOMAIN that is based on a composite type

I would like to define a Postgres domain with check constraints based on a custom composite type. An example for a naive approach would be this: CREATE TYPE raw_comp_foo AS ( min_value integer, max_value integer ); CREATE DOMAIN…
Joerg
  • 790
  • 2
  • 10
  • 23
2
votes
1 answer

cannot add constraint to an existing table

i created a data base 'test' with table table 'user', inside the table there are 'id' and 'name' columns CREATE TABLE `test`.`user` (`id` INT NOT NULL AUTO_INCREMENT , `name` TEXT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB; now i want to add a…
aditya shaikh
  • 21
  • 1
2
votes
2 answers

Simple CHECK Constraint not so simple

2nd Edit: The source code for the involved function is as follows: ALTER FUNCTION [Fileserver].[fn_CheckSingleFileSource] ( @fileId INT ) RETURNS INT AS BEGIN -- Declare the return variable here DECLARE @sourceCount INT ; …
bopapa_1979
  • 8,949
  • 10
  • 51
  • 76
2
votes
1 answer

Force check constraints to be evaluated before computed columns

I want to have a JSON column in a table. I want to have (persisted) computed column that extracts useful information from the JSON data. I want to have a "strict" JSON path but I also want to check that the path exists in the JSON so that the error…
andowero
  • 439
  • 4
  • 13
2
votes
2 answers

How do I create a IF statement creating a table in Postgres?

I'm creating a table and I need a check constraint to validate the posibles values given a string value. I'm creating this table: CREATE TABLE cat_accident ( acc_type VARCHAR(30) NOT NULL CHECK(acc_type = 'Home accident' OR acc_type = 'Work…
Jesus Diaz
  • 123
  • 4
2
votes
2 answers

How to make a PostgreSQL constraint only apply to a new value

I'm new to PostgreSQL and really loving how constraints work with row level security, but I'm confused how to make them do what I want them to. I have a column and I want add a constraint that creates a minimum length for a text column, this check…
Jonathan Plackett
  • 2,346
  • 2
  • 20
  • 33
2
votes
1 answer

Compare expression with constant in CHECK constraint

I'd like to use Django's CheckConstraint to add a check constraint to a table using PostgreSQLs num_nonnulls() function, similar to this: create table foo( a text, b int, [...], check num_nonnulls(a, b, ...) = n); n is a constant…
Feuermurmel
  • 9,490
  • 10
  • 60
  • 90
2
votes
1 answer

check constraint being printed without parenthesis

I have this DDL: CREATE TABLE checkout_value ( id BIGSERIAL PRIMARY KEY, start_value INTEGER, end_value INTEGER, ); With an e-commerce in mind, I want to save several ranges of possible values, where future rules will be applied at…
rado
  • 5,720
  • 5
  • 29
  • 51
2
votes
1 answer

T-SQL: CHECK constraint not working

I have the following T-SQL schema. The problem I am having is that the check constraint on the Download table is not working. I am still able to insert records into that table that contain NULL values for both ProductId and CategoryId. Why is this…
Chris
  • 7,996
  • 11
  • 66
  • 98
2
votes
1 answer

Why aren'y my CHECK CONSTRAINTS on child tables being utilized by the planner to reduce the size of the plan?

I have a table that is partitioned by month and is used for holding apache log information. When I run EXPLAIN for a simple query which has a WHERE clause on the same field as the partition CHECKs, I get the same plan whether constraint_exclusion…
pixelpete
  • 80
  • 3
1 2 3
…
41 42