Stack Overflow
Stack Overflow

Questions tagged [check-constraint]

Check constraints in a relational database ensure that only certain values can be stored in one (or more columns). Check constraints can only validate columns of a single row.

Check constraints validate the value of one or more columns in a single row of a relational table. Typical check constraints are salary > 0 (validating a single column) or hire_date < fire_date (validating the dependency between two columns).

Check constraints can not be used to validate columns between different rows.

All relational DBMS except MySQL support check constraints.

77 questions
30
votes
4 answers

CHECK CONSTRAINT on multiple columns

I use SQL Server 2008 I use a CHECK CONSTRAINT on multiple columns in the same table to try to validate data input. I receive an error: Column CHECK constraint for column 'AAAA' references another column, table 'XXXX'. CHECK CONSTRAINT does…
GibboK
  • 71,848
  • 143
  • 435
  • 658
7
votes
1 answer

How to catch constraint violation in PL/SQL?

CREATE TABLE LOCATION ( LOCID VARCHAR2(5) , MINQTY NUMBER , MAXQTY NUMBER , PRIMARY KEY (LOCID) , CONSTRAINT CHECK_LOCID_LENGTH CHECK (LENGTH(LOCID) = 5) , CONSTRAINT CHECK_MINQTY_RANGE CHECK (MINQTY BETWEEN 0 AND 999) , CONSTRAINT…
user3468536
7
votes
2 answers

SQL check constraint without UDF

I have the following (fictitious) tables: ╔════════════════════╗ ╔════════════════════╗ ║ Course ║ ║ Person ║ ╠══════╦═════════════╣ ╠══════╦═════════════╣ ║ ID ║ int ║ ║ ID ║ int …
Rodolphe
  • 1,689
  • 1
  • 15
  • 32
6
votes
1 answer

Complex Check Constraint?

I have a customers table that links to an addresses table through a middle CustomerAddress table. This means that a customer can have many addresses and an address can have many customers. (This is neccessary due to the fact that we deal with…
Steven Wood
  • 2,675
  • 3
  • 26
  • 51
5
votes
1 answer

String Format Constraint On PostgreSQL Column Not Working

I am moving a database from SQL Server to PostgreSQL and hitting some issues with a check constraint in one of the tables. PostgreSQL version is 9.5.1. I have a table that I created with a check constraint on one of the columns to enforce format.…
K Mo
  • 2,125
  • 8
  • 16
5
votes
1 answer

mysql check constraint where either one of two columns should be null

(Sorry if this is a duplicate post, but I can't seem to find an example for this problem) I have a mysql table with 4 columns like this: SomeTable ========= ID (int) name (varchar(255)) column1 (varchar(255)) column2 (double) What I now want to do…
J. Rahmati
  • 735
  • 10
  • 37
4
votes
1 answer

SQL Server 2008: Check constraints that guarantees that only one value in all rows is set to 1 and others are 0

There is a need to build constraint on the column that guarantees that only one value in all rows is 1 and all the others are 0. Solution with triggers exists but I would like to have something built in. Is such thing possible at all?
Timofey
  • 2,478
  • 3
  • 37
  • 53
4
votes
2 answers

T SQL CHECK Constraint to allow uppercase set of values

I have to create a table in SQL Server using a check constraint to allow only Uppercase set of values My code as this: CREATE TABLE Client( clientID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30) NOT…
user2473015
  • 1,392
  • 3
  • 22
  • 47
3
votes
2 answers

How to use CHECK constraint to restrict non-numeric varchar2 (only alphabets)?

I have a column NAME It must contain only characters and not numbers How do I use CHECK condition: CHECK(NAME NOT LIKE '%[0-9]%') or any other method... edit: Oracle database is used.
Pop Stack
  • 926
  • 4
  • 19
  • 27
3
votes
1 answer

Check constraint for column in SQL Developer

I need to create a table, where the values of column "ttime" can be within the range of 10:00 and 22:00. Here' the code: create table test ( ttime date, CONSTRAINT CHECK_ttime CHECK(ttime >= TO_DATE('10:00', 'HH24:MI') AND …
Ольга Сергеева
  • 31
  • 1
3
votes
2 answers

How can I write a CHECK constraint in SQL that allows a series of strings OR a blank value?

I am trying to write a check constraint for a field in SQL that allows either a series of strings (in this case province names) or a blank value. Current code is this: ALTER TABLE CODEDLOCATION ADD CHECK (ADM1='Central' OR ADM1='East' OR…
Owen
  • 1,652
  • 2
  • 20
  • 24
3
votes
2 answers

Oracle Constraints: mix unique and check constraints

I'm facing the following problem: I would like to create a mix of unique and check constraints. It's probably more business logic than the responsibility of the DB, but still. I'm having a Table with the following structure: desc INSURANCE; Name …
muellae
  • 97
  • 1
  • 9
2
votes
1 answer

Filtered Constraint When Defining a SQL Table?

I wanted to set a unique constraint that allowed for nulls. I came up with filtered constraints. Great. Except... they're all post-table creation. As in: Step 1: define a table Step 2: add the constraint Like this: CREATE TABLE MyTable ( [Id]…
vbullinger
  • 4,016
  • 3
  • 27
  • 32
2
votes
2 answers

Add a column in a table with a first letter must be 0?

I want to add a column S_order_no in a table Sales_order with a constraint that the first letter must start with 0. So that if we try to write the first letter other than 0 it throws an error. Is it possible?. If yes please explain with an example.
Tavish Barua
  • 41
  • 1
  • 4
2
votes
1 answer

CHECK constraint for restricting NULL values based on another column values

I have this very simple piece that's been bugging my brain for a few hours now: CREATE TABLE driving_school_questions( question VARCHAR2(200), picture VARCHAR2(2), CONSTRAINT q_p_chk CHECK ((question LIKE '%see picture%' AND picture IS…
udarH3
  • 199
  • 2
  • 5
  • 16
1
2 3 4 5 6