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.
101
questions
1
vote
1answer
35 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 ...
1
vote
1answer
17 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 ...
0
votes
1answer
17 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 ...
3
votes
2answers
177 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: ...
3
votes
1answer
163 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 ...
2
votes
0answers
39 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 ...
0
votes
1answer
19 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 ...
-2
votes
1answer
26 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 ...
0
votes
1answer
126 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
1answer
16 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 ...
0
votes
0answers
25 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
...
1
vote
1answer
34 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
votes
1answer
726 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}
...
1
vote
1answer
20 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 ...
1
vote
2answers
51 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 ...
2
votes
3answers
138 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 ...
1
vote
1answer
33 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 ...
1
vote
0answers
58 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 ...
0
votes
1answer
65 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
1answer
161 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
1answer
21 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(
...
2
votes
1answer
788 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),
...
2
votes
1answer
342 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 ...
0
votes
1answer
604 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 ...
4
votes
2answers
91 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",...
3
votes
1answer
247 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 ...
2
votes
1answer
217 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 ...
2
votes
2answers
455 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 ...
1
vote
1answer
509 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 &...
1
vote
2answers
346 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 (...
0
votes
2answers
481 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 (...
2
votes
1answer
739 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 ...
0
votes
1answer
65 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 ...
3
votes
1answer
51 views
Ensuring Uniqueness Across Columns
I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution!
Consider the following table:
ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
...
0
votes
1answer
268 views
Table check contraint to verify unique on specific value
Oracle 10g: Suppose the following table (T_REGISTER):
ID_PROCESS PERIOD CUSTOMER STATUS
===========================================
0001234 201801 12300344 INVALID
0001236 ...
4
votes
2answers
486 views
How to make a column mandatory in every Insert / Update (not meaning default null)
Following table:
CREATE TABLE kontrolle.negativtext
(
id serial NOT NULL,
ausschluss character varying(255) NOT NULL,
nur_domains character varying(255)[] NOT NULL DEFAULT '{}'::character ...
1
vote
0answers
191 views
Check all values in a set with PostgreSQL
I'm trying to validate jsonb data with a check constraint to a table that has two fields, header and body, both are jsonb fields, that I expect to be arrays.
Here is an equivalent table:
CREATE ...
1
vote
2answers
2k views
What is the difference between CHECK and CONSTRAINT CHECK?
is there any difference between the follow exemples?
ProjNum int
CONSTRAINT nn_ProjNum NOT NULL
CONSTRAINT C_ProjNum CHECK (ProjNum >= 10)
and
ProjNum int NOT NULL
CHECK (...
0
votes
1answer
34 views
Group by checks constraints are possible?
I have a table with the following columns:
TEAM_ID integer,
NAME varchar(64),
IS_LEADER char(1) not null default 'Y'
I want to apply the constraint that, in the same team, only a leader can be ...
0
votes
1answer
398 views
Order to disable constraint in a SQL Server Database
I have a DB that every day delete all the data and load new one at the night. To do this an SP disable all constraint with sp_msforeachtable execution. I created some new objects (Schemas, SP, Tables, ...
0
votes
1answer
30 views
Create a check constraint across multiple schemas in PostGIS [closed]
I'm a little new to this so apologies up front if my vocabulary is incorrect.
I'm trying to create a check constraint that determines whether a new point is located within an existing polygon. The ...
0
votes
1answer
154 views
Check and trigger constraints [closed]
Suppose a DB contains two tables A and B as given below:
A
---------------------
id SERIAL
id_b INTEGER -- NOT NULL reference to B.id
ts_update TIMESTAMP
B
--------------------------...
1
vote
1answer
45 views
Fire a trigger function when checks change
I have the following use case:
I have a set of different checks on different tables in one schema. If a data set is 'wrong' (did not pass a check) it gets pushed into another table in another schema. ...
18
votes
3answers
10k views
Create a Constraint such that only one of two fields must be filled
I have a table in SQL server where the users need to enter data in either of two columns. That is, One of the two must have data inputted but at the same time i don't want to allow users to input in ...
3
votes
1answer
3k views
Is varchar(x) as fast as `text CHECK ( char_length(x) )`?
In an Twitter exchange, Simon West asks to Brandur,
Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before
...
6
votes
1answer
855 views
Partially-Unique Check Constraints
I have an old Access table which is being migrated to SQL Server, and in it there's a field called "LinkedID". This field was mostly unused until ~5 years ago, so for many historical records it is ...
0
votes
2answers
981 views
Add array table constraints, empty or null if other column is false
I'm adding constraint on an existing table jobs:
DROP TABLE IF EXISTS jobs CASCADE;
DROP TYPE IF EXISTS job_state;
CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' );
...
0
votes
1answer
434 views
Knex pass over db FK constraints
when I try to insert values into my FKs in the DB Browser for SQLite I get the constraints error, but when I run the seeders with knex (npm package) it pass over the FK constraints and it inserts when ...
5
votes
1answer
2k views
Is ALTER TABLE CHECK CONSTRAINT redundant?
I created a table with a foreign key constraint on it. When I generate a table creation script through SSMS, it creates the following code:
ALTER TABLE [dbo].[MainTable] WITH CHECK ADD CONSTRAINT [...
5
votes
2answers
2k views
CHECK constraint for array column to verify length > 0
I'm playing with postgres table validation rules and trying to set a CHECK constraint for an array column. An idea is to allow only arrays with length > 0.
Here is how I want to implement it:
create ...
