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
13
votes
1 answer

Trigger vs. check constraint

I want to add a field-level validation on a table. There is a field named "account_number" and this field should always pass a "luhn" check. I've found a function called "luhn_verify" that seems to work properly (google for it if you are…
David S
  • 12,967
  • 12
  • 55
  • 93
13
votes
1 answer

The values of one column cannot be greater than another

I am trying to create a table where the values in one column can't be greater than the next column over. For example, I am creating the following table. CREATE TABLE Price ( PriceID INT PRIMARY KEY IDENTITY (1,1), OriginalPrice FLOAT NOT…
krikara
  • 2,395
  • 10
  • 37
  • 71
11
votes
4 answers

SQL constraint to prevent updating a column based on its prior value

Can a Check Constraint (or some other technique) be used to prevent a value from being set that contradicts its prior value when its record is updated. One example would be a NULL timestamp indicating something happened, like "file_exported". Once…
andyortlieb
  • 2,326
  • 3
  • 21
  • 33
10
votes
6 answers

Constraint for phone number in SQL Server

Constraint for phone number to be of 7 digits. How to check if it is of 7 digits in SQL Server? CREATE TABLE Customer ( C_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, C_Name VARCHAR(255) NOT NULL, Phone INT );
Serena Gale
  • 109
  • 1
  • 1
  • 6
10
votes
3 answers

PostgreSQL query for a list of allowed values in a constraint?

Given a PostgreSQL table named requests with a column named status and a constraint like this: ALTER TABLE requests ADD CONSTRAINT allowed_status_types CHECK (status IN ( 'pending', -- request has not been attempted 'success', -- request…
seanomlor
  • 973
  • 1
  • 10
  • 23
10
votes
2 answers

Hibernate Check Annotation

I have a table with three fields, say a, b, c. I would like to add a constraint ensuring that if a is not null, then also b and c are not null. I have done that using following SQL ALTER TABLE sample ADD CONSTRAINT no_nulls CHECK (CASE WHEN a IS…
blahblah
  • 1,161
  • 2
  • 14
  • 30
10
votes
3 answers

Can CHECK constraints act like if else?

I have a table with 4 columns: (ID (PK, int, NOT NULL), col1 (NULL), col2 (NULL), col3 (NULL)) I'd like to add a CHECK constraint (table-level I think?) so that: if col1 OR col2 are NOT NULL then col3 must be NULL and if col3 is NOT NULL then col1…
Toby
  • 9,696
  • 16
  • 68
  • 132
9
votes
2 answers

Oracle 11g - Check constraint with RegEx

I'm using Oracle 11g, and trying to create a table define constraints on the creation. I was trying to add check constraint to validate some information (like e-mail address, phone number, etc...) Is there something in Oracle 11g that would allow me…
Pacane
  • 20,273
  • 18
  • 60
  • 97
9
votes
6 answers

What SQL databases support subqueries in CHECK constraints?

What SQL databases, if any, support subqueries in CHECK constraints? At present and as far as I know, Oracle, MySQL, and PostgreSQL do not. EDIT (Clarification based on initial answers.) I'm looking for something like this: CREATE TABLE personnel…
pilcrow
  • 56,591
  • 13
  • 94
  • 135
9
votes
2 answers

Check constraint does not work on bulk insert for more than 250 records

My query : INSERT into PriceListRows (PriceListChapterId,[No]) SELECT TOP 250 100943 ,N'2' FROM #AnyTable This query works fine and the following exception raises as desired: The INSERT statement conflicted with the CHECK constraint …
Mahmoud Moravej
  • 8,705
  • 6
  • 46
  • 65
9
votes
5 answers

In SQL Server 2005, how do I set a column of integers to ensure values are greater than 0?

This is probably a simple answer but I can't find it. I have a table with a column of integers and I want to ensure that when a row is inserted that the value in this column is greater than zero. I could do this on the code side but thought it…
osp70
  • 1,062
  • 1
  • 12
  • 19
9
votes
3 answers

add CHECK constraint to already populated table

I created a table called test with column called code: create table test( code char(3) not null); I then populated the table with the following data: insert into test values ('A12'); insert into test values ('B23'); insert into test values…
j_t_fusion
  • 223
  • 2
  • 7
  • 20
8
votes
5 answers

How do I have a check constraint that refers to another table?

I have the following tables in a SQL Server 2008 db: tblItem, which has an ItemID field; tblGoodItem, which also has an ItemID field, and has a foreign key pointing to tblItem; tblBadItem, which also has an ItemID field, and also has a foreign key…
Tola Odejayi
  • 3,019
  • 9
  • 31
  • 46
8
votes
2 answers

Check if all three columns are either not null or null

I have a table with 4 columns: create table dbo.Table ( Id int not null, A int null, B int null, C nvarchar (4000) null ) How can I make sure that A, B and C are all three null or all three not null?
Miguel Moura
  • 36,732
  • 85
  • 259
  • 481
8
votes
1 answer

NOT NULL constraint on a column when another column has a particular value

create table test ( col1 varchar(20), col2 varchar(20) ) When col1 has value '1', col2 cannot be null. When col1 has any other value, col2 can be null. Is there a way to write a check constraints based on values of particular columns?
sat
  • 5,489
  • 10
  • 63
  • 81
1
2
3
…
41 42