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

Check constraint to allow only one combination of two columns

I have a table to store person's phone numbers. There is a flag (IsDefault) to mark a phone as the default one. There should be only one phone per person flagged as default phone. Is it possible to create a check constraint to only allow one…
Oscar
  • 13,594
  • 8
  • 47
  • 75
2
votes
2 answers

SQL check data period for insert

I have a table with datetime and want to check if there is some entry with datetime in +-30 minutes period relative to inserted value. So i write this constraint: USE [Test] GO /****** Object: UserDefinedFunction [dbo].[CanInsertReception] …
Alex Zhukovskiy
  • 9,565
  • 11
  • 75
  • 151
2
votes
1 answer

Using where clause in Check

I have a situation where a field can be NULL when another field is certain values and for others it should be NOT NULL. "Type" VARCHAR2(30) NOT NULL, BestandLocatie VARCHAR2(150) NULL, I made two constraints, the first…
Politiepet
  • 93
  • 1
  • 2
  • 11
2
votes
2 answers

CHECK constraint not working

I have to create a check constraint on the table based on the below condition. 1. If ColumnX=1 then ColumnY should not be NULL 2. If ColumnX<>1 then ColumnY can be NULL I tried below and seems to be not checking the value. ALTER TABLE…
Billa
  • 5,226
  • 23
  • 61
  • 105
2
votes
0 answers

PostgreSQL Check Contraints not Passed in Join

Consider the following structures, a header and line table, both of which are partitioned by date: create table stage.order_header ( order_id int not null, order_date date not null ); create table stage.order_line ( order_id int …
Hambone
  • 15,600
  • 8
  • 46
  • 69
2
votes
2 answers

Why sp_rename don't work for CHECK constraint?

To rename the CHECK I use query like this: EXEC sp_rename 'test_create_table.test_check.check_1', 'check1', 'OBJECT' Query executes without error but the name of constraint not changed. Some one knows why?
kaa
  • 1,265
  • 5
  • 22
  • 39
2
votes
1 answer

Planner does not use Check Constraints

Please consider the following objects: create table invoices_2011 ( invoice_id bigint not null, invoice_date date not null, constraint invoice_line_2011_ck1 CHECK (invoice_date >= '2011-01-01' AND invoice_date < '2012-01-01') ); create…
Hambone
  • 15,600
  • 8
  • 46
  • 69
1
vote
1 answer

How to constrain the number of records allowed in an SQL table?

Say I have two tables, Parent and Child. Parent has a MaxChildren (int) field and Child has an Enabled (bit) field and a ParentID (int) field linking back to the parent record. I'd like to have a constraint such that there can't be more than…
billpg
  • 3,195
  • 3
  • 30
  • 57
1
vote
3 answers

SQL - Unique key across 2 columns of same table?

I use SQL Server 2016. I have a database table called "Member". In that table, I have these 3 columns (for the purpose of my question): idMember [INT - Identity - Primary Key] memEmail memEmailPartner I want to prevent a row to use an email that…
DoumB
  • 21
  • 2
1
vote
3 answers

SQL CHECK CONSTRAINT to reject alphanumeric and special charters and allow only numeric from 0 to 9

I have the following constraint that is supposed to allow only digits from 0 to 9 insertion and not any special and alpha characters. But that is not the case, for example when using this update statement: update MyDB.dbo.MyTable set…
Data Engineer
  • 795
  • 16
  • 41
1
vote
1 answer

Oracle SQL add check constraint at multiple table level

I have two tables as below. create table emp (empno varchar2(5), position varchar2(5)); create table info (empno varchar2(5), nick varchar2(20)); empno is the primary key for both tables and empno is also a foreign key in info, with parent table…
J. zhao
  • 43
  • 1
  • 7
1
vote
1 answer

SQL: Check constraints syntax errors?

I'm trying to add a constraint to one of my columns, however i get this error message "missing right parenthesis". Not the first time I get this message, however I'm fairly new to SQL, so my syntax is not on par. CREATE TABLE FAGFELT ( bok varchar…
Peter
  • 27
  • 5
1
vote
1 answer

Add Check constraints in SQL Server: ADD CONSTRAINT vs ADD CHECK

I want use a check constraint for a column in any table in SQL Server 2008. I would like give a qualified name to the check constraint. I have seen several syntax version on how to create it: ALTER TABLE [dbo].[Roles2016.UsersCRM] WITH CHECK …
Kiquenet
  • 14,494
  • 35
  • 148
  • 243
1
vote
1 answer

Check constraint not working in mariaDB

I created a table using the following statement. create table constraint_test(name varchar(20), city varchar(20) not null check (city in ('chennai','vellore')), phone numeric(10)); But when I insert as, insert into constraint_test…
Naveen Kumar V
  • 2,559
  • 2
  • 29
  • 43
1
vote
1 answer

Check constraints for varchar[] and varchar[][] columns in a table

For a word game I am trying to add CHECK constraints to the VARCHAR arrays: CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, created timestamptz NOT NULL, player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
1
2
3 4 5 6