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.
118
questions
-1
votes
1
answer
56
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][...
- 147
4
votes
1
answer
129
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 ...
- 481
1
vote
3
answers
405
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 [...
- 37
0
votes
1
answer
42
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 ...
- 101
0
votes
2
answers
111
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 ...
- 33
1
vote
2
answers
253
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 ...
- 13
2
votes
2
answers
335
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 ...
- 123
6
votes
1
answer
294
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".
...
-3
votes
1
answer
401
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?
- 3
0
votes
2
answers
261
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 :
...
- 167
0
votes
1
answer
112
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 ...
- 125
0
votes
1
answer
67
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 ...
- 323
1
vote
1
answer
2k
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| ...
- 115
0
votes
1
answer
35
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` ...
- 149
1
vote
1
answer
83
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
...
- 111
1
vote
3
answers
207
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 ...
- 13
1
vote
1
answer
142
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'...
- 314
1
vote
1
answer
98
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 ...
- 113
2
votes
1
answer
336
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 ...
- 173
0
votes
1
answer
46
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 ...
- 1,728
3
votes
2
answers
202
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: ...
- 141
3
votes
1
answer
1k
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 ...
user223014
2
votes
0
answers
48
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 ...
- 159
0
votes
1
answer
144
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 ...
- 101
-2
votes
1
answer
31
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 ...
- 13
0
votes
1
answer
506
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 ...
0
votes
1
answer
28
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 ...
- 101
0
votes
0
answers
34
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
...
- 33
1
vote
1
answer
94
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) ...
- 13
13
votes
1
answer
1k
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}
...
- 78.6k
1
vote
1
answer
159
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 ...
- 183
1
vote
2
answers
134
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 ...
- 169
2
votes
3
answers
392
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 ...
- 121
1
vote
1
answer
50
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 ...
- 121
1
vote
0
answers
62
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 ...
- 11
0
votes
1
answer
223
views
Sqlite - Limit Characters for different parts of one String with Check
I have to make sure that any inserted Email has the format: "X@Y.Z"
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 ...
0
votes
1
answer
283
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 ...
0
votes
1
answer
33
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(
...
3
votes
1
answer
2k
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),
...
- 767
4
votes
1
answer
997
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 ...
- 497
0
votes
1
answer
1k
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 ...
- 103
4
votes
2
answers
116
views
A table with all fields NULLable with important, sensitive data
I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc).
On screen the information is sectioned into tabs - "alcohol",...
- 143
3
votes
1
answer
426
views
Enforcing a "disjoint or identical" constraint
Suppose that F is an Oracle table with two columns, X and Y, such that the constraint
CONSTRAINT unique_xy_pairs UNIQUE (X, Y)
is satisfied.
For each value [x] of F.X, let S[x] represent the set ...
- 331
2
votes
1
answer
723
views
Building a conditional check constraint
I'm trying to build a conditional check constraint for my table TBL_AFIL, which has two fields:
Fec_Renun DATE
flg_afil INTEGER
Constraint: IF flg_afil=1 THEN Fec_Renun can not be NULL.
ALTER TABLE ...
- 719
2
votes
2
answers
2k
views
Add Constraint to check value exists in a non-unique composite key column
I have the following table, which has a composite primary key:
CREATE TABLE dbo.Seqs(
SequenceKey nvarchar(10) NOT NULL,
UsageSequence smallint NOT NULL,
col1 bit NOT NULL,
col2 ...
- 285
1
vote
1
answer
2k
views
How to Add Check Constraint between Tables (or change DDL)?
Given the following PostgreSQL table definitions:
postgres=# create table if not exists messages(
postgres(# id serial primary key,
postgres(# max_length smallint not null check (max_length &...
- 475
1
vote
2
answers
690
views
Postgres list constraints that use a stored procedure
I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.
something like:
CREATE TABLE payment (
amount bigint,
CONSTRAINT amount_gt_0 CHECK (...
- 121
0
votes
2
answers
711
views
Set a check constraint for a set of conditions which aren't universally true [closed]
I need to make sure my database is self-consistent. There are two columns, which share a relationship when there is some specific value the other should be not null. I tried using constraint check (...
- 151
2
votes
1
answer
1k
views
Created Check Constraint WITH NOCHECK - still trusted
I needed to alter the conditions of existing constraint - so I dropped it and recreated with the new conditions.
This failed, because there were 800k+ rows, that didn't meet the condition. That ...
- 2,350
0
votes
1
answer
151
views
Error on Check Contraint that is on a Column with a Default Constraint
Inspired by this article, I wanted to test the idea before I considered implementing it.
However, I get this error
Msg 547, Level 16, State 0, Line 14 The INSERT statement conflicted
with the ...
- 773