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
1
vote
1 answer

Check Constraint with Filtering Logic

I have a table that has bit column called "Flag" it does not allow NULL. A requirement here is to ALWAYS have Flag=1 set for every Id in the table below but only 1 time per each unique Id and Flag columns combination. At the same time all other rows…
Data Engineer
  • 795
  • 16
  • 41
1
vote
1 answer

Avoiding multiple "OR's" in a SQL query

I have the following working SQL query that adds a constraint MindestensEinKontakt_CHECK to the table KundenKontaktDaten. The constrainst ensures that at least one of the attributes Twitter_Id, Google_Id, Facebook_Id, Skype_Id, and Telefonnummer is…
Monika
  • 301
  • 3
  • 12
1
vote
1 answer

How to remove check constraint?

I am having difficult to remove CHECK using Alter in sql. Can anyone help me please? CREATE TABLE MyProject_COST ( ID int(4) NOT NULL UNIQUE, detail varchar2(25) NOT NULL, cost int(6) CONSTRAINT cost_project CHECK(cost>=500) ); ALTER…
kodder360
  • 99
  • 7
1
vote
1 answer

Looking for a strategy for user friendly CheckConstraint error messages in sql server

I'm setting up a database and there are several tables where there are many inter-row dependencies that need to be checked prior to creating/updating a row. There are many fields that have max/min/avg/stdev from many sources. I figured that I…
Hucker
  • 671
  • 1
  • 8
  • 25
1
vote
1 answer

Check constraint before insert a value into the database

I'm trying to create a table to store order_details. The table has a column status that should only be allowed to contain the value true once per each order_id, but may contain the value false multiple times per order_id. create table order_details…
duniyaKaRakhwala
  • 35
  • 1
  • 4
1
vote
1 answer

Find the row violating the constraint to be added

I'm trying to add a check constraint to a table like ALTER TABLE foo ADD CONSTRAINT bar CHECK (...); and I get the error: ERROR: check constraint "bar" is violated by some row ********** Error ********** ERROR: check constraint "bar" is violated…
tom
  • 2,137
  • 2
  • 27
  • 51
1
vote
2 answers

MySQL: how to make an attribute "nullable" only if certain condition

I am creating a table for a DB, and I would like (if possible) to do something like this: Attribute X can be NULL if, and only if, attribute Y is "value1". Is there a way to do this? I want to do this because I could delete an entity, reducing the…
Petaurus
  • 21
  • 1
1
vote
2 answers

Check constraint with function fails on update although function returns correctly

I am trying to implement a CHECK constraint on table CheckTable that ensures only one row for a given number of rows with same col2 value can have the ok bit set to 1. This is my go at the problem, and I am struggling to understand how to use the…
noontz
  • 1,782
  • 20
  • 29
1
vote
1 answer

Constraint on postgresql

Hi I have a Receipt table (Id, total, Issue Date, Status, Operator) and an Employee table (ID, Operator ID, ...). I have to define the following constraint: An operator cannot have more than one active receipt (status) at the same time per day.…
user12818580
1
vote
3 answers

How to call a PL SQL function within a CHECK statement?

I would like to add a CHECK statement that calls a function when inserting new entries into a table. I have used the following sample code to implement such functionality: CREATE TABLE customers( id NUMBER NOT NULL, PRIMARY KEY(id)); CREATE…
Matt
  • 796
  • 12
  • 25
1
vote
1 answer

SQLite, ASCII Characters 0x20 to 0x7E Check Constraint

I have the following table where I'm trying to limit the column "name" to ASCII characters ranging from 0x20 to 0x7E (all printable ASCII characters). Why is my check constraint not working? CREATE TABLE test ( id INTEGER NOT NULL, name…
sqlearner
  • 97
  • 5
1
vote
2 answers

How to add validation on a phone number?

How would I add validation on a phone number to be 11 numbers but start with the number 0 and allow +- etc? But still, be a bit flexible by allowing numbers. My lecturer told me to use an 'OR' statement but I'm not sure how to get it working I have…
Annon
  • 123
  • 1
  • 9
1
vote
2 answers

Check constraint depending on another table (mutually exclusive relation)

I need help with a project, a DB for hotel administration . I have three tables, bookings, check_ins and cancellations. I consider that any booking can end either as an cancellation or a check-in, but not both simultaneously. The fact is that I…
ada109
  • 53
  • 4
1
vote
1 answer

How to set Specific Asymetric Constraint in Orbeon Form

I am trying to build an Orbeon form where I want to capture Number (along with hyphen or Space) in specific format. For example: Field1 -> 11 1111 1111111 11 or 11-1111-1111111-11 I tried adding Constraint formula matches(., "^\s*\d{15}\s*$") which…
Aman Shah
  • 25
  • 5
1
vote
1 answer

Check constraints-1

I am wondering about unclear point to me when I am defining check constraint on the column in a table as following : Create test ( emp_id number(8) , salary number(9,2) constraint test_sal_check CHECK(salary>0,emp_id between 0 and 9) ); In this…
oraclejunior1
  • 37
  • 2
1 2 3
…
41 42