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

Checking foreign key constraint "online"

If we have a giant fact table and want to add a new dimension, we can do it like this: BEGIN TRANSACTION ALTER TABLE [GiantFactTable] ADD NewDimValueId INT NOT NULL CONSTRAINT [temp_DF_NewDimValueId] DEFAULT (-1) WITH VALUES -- table is not…
Serguei
  • 2,910
  • 3
  • 24
  • 34
8
votes
3 answers

How to write a query to ensure email contains @

I'm creating a database in db2 and I want to add a constrain to validate whether the user inserting a valid email address, that contain %@%.% . Without luck...any advice?
Mindan
  • 979
  • 6
  • 17
  • 37
7
votes
3 answers

Can an SQL constraint be used to prevent a particular value being changed when a condition holds?

I know that SQL constraints can force data to meet validity criteria. However, what about criteria such as "Student's grade can only be updated when the 'finalised' flag is false"? Do such update criteria have to be handled by the application?
Robin Green
  • 32,079
  • 16
  • 104
  • 187
7
votes
1 answer

Is it not possible to have a check constraint reference a column that also has a foreign key?

I'm on MySQL 8 trying to add a check constraint: ALTER TABLE `table` ADD CHECK ( (`column_a` IS NULL AND `column_b` IS NOT NULL) OR (`column_a` IS NOT NULL AND `column_b` IS NULL) ); but I keep getting this error: Column 'column_b' cannot…
Shoe Diamente
  • 723
  • 1
  • 5
  • 24
7
votes
1 answer

Django CheckConstraint: Elements of reverse ForeignKey lookup must not be empty

I've got these models: class Container(models.Model): ... class Meta: constraints = [ models.CheckConstraint( check=~Q(elements=None), name='container_must_have_elements' ), …
Peter F
  • 3,633
  • 3
  • 33
  • 45
7
votes
3 answers

Postgresql: Violates check constraint. Failing row contains

I tried to insert some data in a database with postgresql but still showing the same message: ERROR: new row for relation "empleados" violates check constraint "ck_empleados_documento" DETAIL: Failing row contains (13, 22222222, f, Lopez,…
Jonathan Gómez Pérez
  • 267
  • 2
  • 4
  • 14
7
votes
2 answers

When are check constraints evaluated?

I have a table of Records ID EntityID Value Status and a table of Entities ID Col1 Col2 CurrentRecordID CurrentRecordID should be the Record tied to the Entity with a Status of 0 I have two check constraints One on table Entity that checks to…
MrZander
  • 3,031
  • 1
  • 26
  • 50
7
votes
3 answers

Using a case statement in a check constraint

i'v been learning SQL for the last week but I am unsure how to correctly add a case statement within a check constraint. Can anybody give me any pointers? I have the following grade table: CREATE TABLE Grade ( salary_grade char(1) NOT NULL…
user3414871
  • 99
  • 1
  • 1
  • 8
7
votes
2 answers

Check Constraint – only allow one column to be true if another column is true

Take the following example table: CREATE TABLE [dbo].[tbl_Example]( [PageID] [int] IDENTITY(1,1) NOT NULL, [RequireLogin] [bit] NOT NULL, [RequireAdmin] [bit] NOT NULL, [HideIfLoggedIn] [bit] NOT NULL ) How would one rewrite the…
HeavenCore
  • 7,533
  • 6
  • 47
  • 62
6
votes
7 answers

Checking sql unique value with constraint

I have a situation where a table has three columns ID, Value and status. For a distinct ID there should be only one status with value 1 and it should be allowed for ID to have more then one status with value 0. Unique key would prevent ID of having…
mko
  • 6,638
  • 12
  • 67
  • 118
6
votes
1 answer

Unique constraint with defined value

given the following sample table structure is there a way to add to a unique constraint to insure uniqueness for (GUID, 'Y') combination? Application logic - update by guid generates a new version with same guid but new luid; and previous goes…
Konstantin
  • 3,254
  • 15
  • 20
6
votes
1 answer

How to create check constraint in mysql diagram view?

Lets say I have a table like this: CREATE TABLE IF NOT EXISTS newbook_mast (book_id varchar(15) NOT NULL UNIQUE, book_name varchar(50) , isbn_no varchar(15) NOT NULL UNIQUE , cate_id varchar(8) , …
CodingLittle
  • 1,761
  • 2
  • 17
  • 44
6
votes
1 answer

Custom error message for Postgresql CHECK IN list constraint

I would like to create a more specific error message for Postgres CHECK IN violations. So for example a violation of the following CHECK constraint on a column: management_zone varchar(15) NOT NULL CHECK (management_zone IN ('Marine', 'Terrestrial')…
Ric
  • 75
  • 1
  • 8
6
votes
3 answers

plsql oracle check in constraint error

Im getting this error: ORA-00904: "M": invalid identifier --> if I put ('M','F') //single quotation i got this error message: PLS-00103: Encountered the symbol "M" when expecting one of the following: * & = - + ; < / > at in is mod remainder not…
Mariya
  • 847
  • 1
  • 9
  • 25
6
votes
2 answers

MySQL - creating table with not null on multiple columns together

Is there anyway to create a table with multiple columns and 2 of them should never be null in same record. for example, I need to make C and D somehow that each one of them could be null if the other wasn't null in same record. I there any way? | A…
Pejman
  • 2,442
  • 4
  • 34
  • 62
1 2
3
…
41 42