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

Alternative to using subquery inside CHECK constraint?

I am trying to build a simple hotel room check-in database as a learning exercise. CREATE TABLE HotelReservations ( roomNum INTEGER NOT NULL, arrival DATE NOT NULL, departure DATE NOT NULL, guestName CHAR(30) NOT NULL, …
Markov
  • 61
  • 1
  • 2
6
votes
1 answer

How to declare CHECK constraint with Spring JPA?

How is it possible to know, which data annotations are supported by Spring JPA? For example, is @Min supported? @Min(value = 0) private double calories; Anyway, how can one declare CHECK constraint? UPDATE Please read about CHECK constraint.…
Dims
  • 47,675
  • 117
  • 331
  • 600
6
votes
1 answer

Why does this Oracle DROP COLUMN alter the default value of another column?

We have a weird situation on a table in an Oracle database where dropping a column results in changing the default values of another column. Here's the scenario. I have my table with some sample data in it : select * from SAMPLE_TABLE ; ID …
ddewaele
  • 22,363
  • 10
  • 69
  • 82
5
votes
2 answers

A nicer way to write a CHECK CONSTRAINT that checks that exactly one value is not null

Imagine that I have a table with integer columns Col1, Col2, Col3, Col4. Each column is nullable and a valid row must contain a value in exactly 1 columns (i.e. all nulls is invalid and more than 1 column is also invalid). At the moment I have a…
Chris Fewtrell
  • 7,555
  • 8
  • 45
  • 63
5
votes
3 answers

How to create a check constraint between two columns in SQL?

I am trying to create a Basic pay (BP) table with CREATE TABLE bp ( bpid VARCHAR(5), FOREIGN KEY (bpid) REFERENCES designation(desigid), upperlimit DECIMAL(10,2) NOT NULL, lowerlimit DECIMAL(10,2) NOT NULL, increment …
Unknown
  • 71
  • 1
  • 1
  • 4
5
votes
2 answers

How to reference other tables in check constraints?

I have a table, ProductSupportArticles: ProductSupportArticleID int NOT NULL ParentArticleID int NULL ProductID int NOT NULL Title varchar(100) NOT NULL Content varchar(MAX) NOT NULL ProductID is a foreign key to Products.ID,…
Jake Petroules
  • 23,472
  • 35
  • 144
  • 225
5
votes
1 answer

Exact length column in SQL Server

Is there a way to make a column have a contraint of exactly so many characters? I have a string of 152 characters, and want the column to only accept values that are 152 in length, not 151, not 153. I know char can handle the overflow, but what…
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
5
votes
3 answers

Using a check contraint in MySQL for controlling string length

I'm tumbled with a problem! I've set up my first check constraint using MySQL, but unfortunately I'm having a problem. When inserting a row that should fail the test, the row is inserted anyway. The structure: CREATE TABLE user ( id INT UNSIGNED…
ptrn
  • 4,902
  • 6
  • 28
  • 30
5
votes
3 answers

Oracle: how to formulate a complex constraint with case when

The following conditional constraint simply doesn't work. Is it possible to formulate in a working way? ALTER TABLE eni_trasc_voci_pwr_fatt ADD CONSTRAINT tvp_constraint_1 CHECK ( CASE WHEN TVP_CODICE_ASSOGGETAMEN = '-' THEN tvp_regione IS…
Revious
  • 7,816
  • 31
  • 98
  • 147
4
votes
1 answer

Oracle SQL - can CASE be used in a check constraint to determine data attributes?

I'm using Oracle 10g and I want to apply a constraint to a table where the value entered for one column determines whether another column IS NULL or IS NOT NULL. Column1 can only contain 1 or 0; Column2 is VARCHAR2(255). I know that the following…
blizzard242
  • 43
  • 1
  • 4
4
votes
2 answers

Why `IF` clause is not possible in postgres `CHECK` constraint?

for example is this query possible? CREATE TABLE products ( name text, price numeric not null, CHECK (IF price > 0 THEN name IS NOT NULL ELSE name IS NULL END IF) ); UPDATE: seems like no here…
srghma
  • 4,770
  • 2
  • 38
  • 54
4
votes
2 answers

SQLite long-arm check constraint?

I have two tables in SQLite, modeling a one-to-many relationship: CREATE TABLE parent ( id INTEGER PRIMARY KEY, payload TEXT ); CREATE TABLE child ( id INTEGER PRIMARY KEY, flag BOOLEAN, parent_id INTEGER, FOREIGN…
Jinghui Niu
  • 990
  • 11
  • 28
4
votes
3 answers

Find only user-defined check constraints in Oracle ALL_CONSTRAINTS

Consider this table definition: CREATE TABLE foo ( a int not null, -- Implicit not null constraint b int check (b is not null), -- Explicit not null constraint c int check (c > 1) -- Explicit constraint ); I want to…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
4
votes
3 answers

Check Constraint based on other column value

I have a table named Table1 with three columns col1, col2, col3. col1 can have one of the three values(1 or 2 or 3). I need to add a check constraint such that it checks, if col1 has value 1 then the values of col2 and col3 should be same else if…
Indhumathi
  • 41
  • 1
  • 5
4
votes
1 answer

How to recheck primary/foreign key constraint for data already in the table in sql server?

I have a table in SQL Server 2005 with a foreign key and it was disable for huge data loading, and then re-enabled: Example: alter table table1 nocheck constraint fk_1 go lots of inserts... go alter table table1 check constraint fk_1 go Now, the…
Julio Furquim
  • 85
  • 6
1 2 3
…
41 42