Questions tagged [check-constraints]

That part of declarative referential integrity (DRI) where a column's permitted range of values is enforced by a Boolean predicate.

Filter by
Sorted by
Tagged with
0 votes
2 answers
56 views

Correct (as in normalized) way to express a relationship between 3 tables with complex(?) constraints?

I'll be using Postgres for the examples, but feel free to show examples in other databases if needed. The simplified schema: create table factory ( id serial primary key, detail text not null ); ...
Cidos's user avatar
  • 11
0 votes
2 answers
95 views

DATE Constraint is ignored on insert

I must be missing something somewhere.. Azure SQL DispatchedOn is a DateTime column ALTER TABLE [table] WITH CHECK ADD CONSTRAINT [CK_ReportDate6MonthRollGreater] CHECK ((CONVERT([date],[...
tbasallo's user avatar
  • 177
2 votes
1 answer
52 views

Constraint checks on multiple association tables vs. unique association table that centralizes these constraint checks

I have the following database schema (MySQL 5.7): I have assistants that may be linked to either: one customer (might evolve to multiple customers in the future, hence the association table) one or ...
Merinorus's user avatar
0 votes
1 answer
24 views

Why constraint fails on preventing the insert statement execution?

I have the following table: -- laravel.spies definition CREATE TABLE `spies` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `surname` varchar(255) NOT NULL, ...
Dimitrios Desyllas's user avatar
1 vote
3 answers
158 views

Alter a scalar UDF that is used in a check constraint in SQL Server 2012

I have a check constraint on a column that uses a scalar UDF. I have a requirement where I need to make changes to the UDF. Based on what I know, I need to drop the constraint, make changes to the UDF,...
RobMartin's user avatar
  • 149
0 votes
0 answers
131 views

Detailed error message from check constraints using exceptions?

I am trying to get a detailed error message when a check constraint is violated (in PostgreSQL). One way to achieve that is to define a check constraint using a UDF that throws exceptions. However, ...
FrequentGuest's user avatar
0 votes
0 answers
156 views

How to include "CHECK" constraint to backup done via phpMyAdmin?

When I export my database from phpMyAdmin (quick method), it does not include CHECK constraints. However, when I run SHOW CREATE TABLE table, I see the constraints. Moreover, foreign constraints are ...
xerez's user avatar
  • 1
8 votes
1 answer
751 views

Why won't this UDF work in this check constraint?

I’m trying to use a UDF as an alternative to using a SELECT command within a check constraint. I need to put a constraint on the report table to ensure the signedBy field is the PK of a personnel ...
agerber85's user avatar
  • 111
0 votes
1 answer
25 views

Ensure value is in list when inserting / updating

is it possible to ensure that a value is in a list of values defined in another table? For instance by using a check constraint? I was thinking about a construct like this: CREATE OR REPLACE TABLE `...
Markus's user avatar
  • 101
-1 votes
1 answer
135 views

What characters are rejected during this check constraint clause in a SQL Server stored procedure?

I tried to import a record with a semicolon in the addr field. Once I removed the semicolon from the import file, the following error went away: Database error occurred SQLSATE = 23000 [Microsoft][...
Sun's user avatar
  • 147
4 votes
1 answer
519 views

What is the current state (2022) of using UDFs in a CHECK constraint in SQL Server?

SQL Server allows the use of User-Defined Functions (UDFs) in CHECK constraints - which has a number of use-cases - which I won't get into - but I was recently faced with implementing a non-trivial ...
Dai's user avatar
  • 564
1 vote
3 answers
734 views

How Does SQL (In my case SQL Server) Evaluate a Unique Constraint On Multiple Columns?

Given the following table CREATE TABLE example ( id int NOT NULL IDENTITY(1,1), strA NVARCHAR(10) NULL, strB NVARCHAR(10) NULL, CONSTRAINT [pk_id] PRIMARY KEY (id) CONSTRAINT [...
Catachan's user avatar
0 votes
1 answer
54 views

Impose limits on number of records stored in a table

Assume we have a table with following schema: favorites{ UserID CategoryID } How can I enforce a policy which ensures each users has at least 3 categories and at most 5 categories. Is it ...
milad's user avatar
  • 101
0 votes
2 answers
753 views

Validate a column if it contains a character, using check constraint in postgresql

Table: create table tempchkconstraint ( colcode varchar, colname varchar, lastmoddate timestamp without time zone ); Need to validate the column 'colcode' if any data with character '-' is ...
jerin kj's user avatar
1 vote
2 answers
2k views

Allow SQL column to only be updated if NULL

tldr: Is it possible to create a constraint that enforces: only allow update to column if its current value is NULL. I’m using Postgresql to update a column from NULL to a foreign key when the user ...
stonefree's user avatar
2 votes
2 answers
2k views

Create unique index based on two columns (bi-directional)

I want to make a check constraint or a unique index to validate that I'm not creating duplicates within my table based on 2 columns, I do not want the same two IDs in these columns. Thinking about ...
Jeggy's user avatar
  • 123
6 votes
1 answer
397 views

SQL Server Scalar UDF Parallelism Mystery!

Referencing this blog post by Erik Darling on the website of my favorite SQL Server guru - Brent Ozar: When you select from that table alone, it shows "CouldNotGenerateValidParallelPlan". ...
Anil Raghavan's user avatar
-3 votes
1 answer
1k views

How to store only US/Canada phone numbers in postgres?

I want to store only US and Canada phone numbers in a Postgres TEXT column. I don't want to use pg_libphonenumber. What CHECK constraints should I be using for this purpose?
yayaur's user avatar
  • 3
0 votes
2 answers
1k views

constraint check values with regular expression

In a table with a field code, I want to authorize the user to choose one value among a list. Here is a sample of the values of the list : A|AG|AL|AS|B|C|D|DA I added a constraint on code like this : ...
Leehan's user avatar
  • 207
0 votes
1 answer
338 views

data model limit rows per user

This is regarding to postgres that I was asked to create data model which has a relationship with user. For example a new data model called Comment M:1 User, but while creating the data model is it ...
Dora's user avatar
  • 125
0 votes
1 answer
84 views

Does putting constraints decrease the required size to store my data in SQL Server?

I would like to know if putting check constraints decrease the required column size or not? For example, when I create an int column and a check constraint on it to store the numbers between 100 and ...
Commander's user avatar
  • 333
5 votes
1 answer
7k views

Column constraint based on values in another column

I have a PostgreSQL table phase_steps, with the following example rows: phase_step_id|step_type|step_status| -------------+---------+-----------+ 1| RESEARCH| | 2| ...
Rudy Stricklan's user avatar
0 votes
1 answer
262 views

how do I restrict a specific row's columns to have only a specific value set?

I have the following table CREATE TABLE `mview_state` ( `state_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'View State ID', `view_id` varchar(255) DEFAULT NULL COMMENT 'View ID', `mode` ...
gabtzi's user avatar
  • 149
1 vote
2 answers
419 views

Check constraint to guarantee ltree ancestors

I ran into an issue where we had a tree structure that looked like this: select title, tree_path from my_table where tree_path <@ '3'; title | tree_path ----------------------- item A | 3 ...
lorddev's user avatar
  • 111
1 vote
3 answers
546 views

Constraint to prevent immediate loop in hierarchy

I have a companies table: CREATE TABLE companies ( id bigserial, name varchar(255) NOT NULL PRIMARY KEY (id) ) I want companies to have parents and childs. A company might have many parent and ...
bruno's user avatar
  • 13
1 vote
1 answer
440 views

Is it more efficient to create check constraints on PostgreSQL table before or after bulk load?

I am creating a table in PostgreSQL and bulk load with data first. PostgreSQL's documentation recommends to insert large volumes of data first and then create indexes and foreign key constraints. What'...
k_rus's user avatar
  • 314
1 vote
1 answer
192 views

Add constraint to table creation in SQL

I have to create a table Product with elements P, PType, PName, Price and Origin. I need to have the constraint that all products with PType "Meat" must have "Europe" as Origin. I ...
Ele975's user avatar
  • 113
2 votes
1 answer
878 views

Retrieve Names of Check Constraints Specific to a Column

PostgreSQL 12 When I rename a column in a table, I would like to dynamically rename the matching check constraints as well. I have a hypothetical table: CREATE TABLE hypothetical_table ( id SERIAL ...
Avocado's user avatar
  • 223
0 votes
1 answer
115 views

PostgreSQL - Removing Unused Index on Check Constraint column

I used the script from this blog to identify the unused index on my database. It returns an index which is on a column called result where I have a check constraint on that column. So removing this ...
TheDataGuy's user avatar
  • 1,910
3 votes
2 answers
238 views

How to implement an association with restrictions

I'm working on an SQL database of useful plants. I'd like to be able to allow users to annotate the plants with a set of hierarchical categories and tags. For example: category: medicine body part: ...
Joaquim d'Souza's user avatar
3 votes
1 answer
2k views

CHECK Constraint to enforce upper case letter and pattern match

For one of my columns I am trying to enforce a pattern. The first letter should be D upper case, and the remaining 3 characters should be digits. For example: D678, D890, D000 I'm quite new with CHECK ...
user avatar
2 votes
0 answers
126 views

Check constraint on a pair of columns

I have a table with this structure: create table licence_report ( id bigint default nextval('licence_report_id_seq'::regclass) not null, created ...
Pavanraotk's user avatar
1 vote
1 answer
555 views

Adding a check constraint on a new column with default value on a large table

I have a large table (1–2m rows) and need to add a column. It is an integer column with a default of 0. I am also adding a check constraint that column >= 0. Currently, I am making the change in ...
Chris's user avatar
  • 111
-2 votes
1 answer
43 views

CHECK CONSTRAINT isn't identifying breaches of a constraint but DBCC CHECKCONSTRAINTS does

I have a table full of data and I have a constraint on a field in that table that is setup to ensure the total of 2 fields equals a 3rd field. For example: ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD ...
Alex's user avatar
  • 13
0 votes
1 answer
953 views

Check constraint age 18< [closed]

Hello I need to create a constraint that chekc the user is 18< before listenning to certain songs but idk how... I tried this : ALTER TABLE Historique_ecoute DROP CONSTRAINT IF EXISTS ...
Ohmy Zala's user avatar
0 votes
1 answer
32 views

Adding a dropped check-constraint what queries the table

I'm using a Postgres to preist a hierarchical data set; the table I'm using is something like this: CREATE TABLE layers ( bucket text not null, uniq_id serial not null, name ...
Reza Same'ei's user avatar
0 votes
0 answers
61 views

Check constraint on grouped conditions

Take the following table as an example: GroupID AlphaID BetaID 1 0 1 1 1 1 1 2 2 2 0 1 2 1 2 ...
Levi's user avatar
  • 33
1 vote
1 answer
400 views

MySQL - is there a way to implement a CHECK constraint after creating the table?

I created a table and after entering all the data, I found that I had missed a CHECK constraint in the CREATE TABLE statement. create table jims_faculty ( fac_id int primary key, fac_name varchar(15) ...
AMAN MALIK's user avatar
14 votes
1 answer
2k views

How can I enforce that values in a table column match the Regular Expression "[axyto0-9\s]{0,2}[\s0-9]{0,10}"?

I have a table as below CREATE TABLE dbo.DemoTable ( Value VARCHAR(12) ) I would like to constrain it to only contain rows where Value matches the following pattern [axyto0-9\s]{0,2}[\s0-9]{0,10} ...
Martin Smith's user avatar
  • 83.3k
1 vote
1 answer
432 views

Can I tell Postgres that a new constraint is valid without actually running the check?

I have a constraint that ensures one of three columns is populated, and I'm adding a new column, so I'm replacing the old constraint with a four-column version. Since I know from the old constraint ...
Qaz's user avatar
  • 193
1 vote
2 answers
591 views

Should fields that are set to Not Null also have a constraint refusing empty strings?

Any field that I would expect a value (Like an orderNumber or customerNumber) I would set as not nullable. If I'm expecting the value of the field to be not null, shouldn't I also always have a ...
Elias's user avatar
  • 169
2 votes
3 answers
1k views

Foreign key reference to column that is no primary key

I know, foreign keys should uniquely identify rows and therefore reference a primary key. However, consider my example: There are 4 pre-defined categories, and for some combinations of values for ...
David's user avatar
  • 121
1 vote
1 answer
58 views

Adding constraint to an existing table column

I have a table that doesnt have any constraint on them like this CREATE TABLE SomeTable ( Id int NOT NULL, Type varchar(50), ) insert into SomeTable values (12,'Exchange-Student'); Now I ...
zellez's user avatar
  • 121
1 vote
0 answers
64 views

Best way to handle this type of constraint

Guidance please. We have a small configuration table where we need to ensure only 1 record has bit checked. In the past, we have done check constraint with function that checks count, which we are ...
Amanda's user avatar
  • 11
0 votes
1 answer
491 views

Sqlite - Limit Characters for different parts of one String with Check

I have to make sure that any inserted Email has the format: "[email protected]" X and Y may only contain letters and numbers and Z can only have Letters. I tried something like this, but i cant figure out how ...
dbs-sqlite-novice's user avatar
0 votes
1 answer
443 views

SQLite, ASCII Characters 0x20 to 0x7E Check Constraint

I have the following table which I'm trying to limit the column name to ASCII characters ranging from 0x20 to 0x7E. Why is it not working? CREATE TABLE test ( id INTEGER NOT NULL, name TEXT ...
sqlearner's user avatar
0 votes
1 answer
42 views

Constraints between tables on a variable

im aiming to add a constraint on the number of bicycles allowed to be stored in each terminal - the number differing between terminals. These are my original tables create table terminal( ...
Matthew Streeter's user avatar
3 votes
1 answer
3k views

Compound unique constraint with NULL values

With the following table structure CREATE TABLE favorite_comments( id SERIAL PRIMARY KEY NOT NULL, opinion_id BIGINT REFERENCES opinions(id), review_id BIGINT REFERENCES reviews(id), ...
Imanol Y.'s user avatar
  • 785
5 votes
1 answer
2k views

Constrain array values to an allowed set of values

I would like to set up a CHECK constraint that makes sure that a text[] column's elements contain only certain values. Setting up an example: CREATE TABLE foo( countries text[] ); INSERT INTO foo ...
AndreKR's user avatar
  • 557
0 votes
1 answer
2k views

Create a foreign key to a table that doesn't exist in postgres

I'm trying to put together a solution where I have a bunch of script files that contain my schema creation. Something similar to Visual Studio's SQL Project, but for Postgres. My problem is that the ...
WynDiesel's user avatar
  • 103