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
1answer
43 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
355 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 ...
1
vote
1answer
53 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
33 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 ...
25
votes
2answers
37k views

ALTER TABLE CHECK CONSTRAINT

From the Object Explorer in SQL Server, when selecting and scripting a foreign-key constraint, the following code is generated. USE [MyTestDatabase] GO ALTER TABLE [dbo].[T2] WITH ...
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( ...
0
votes
2answers
168 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 (...
1
vote
1answer
168 views

CHECK constraint to limit certain rows

I have the task to create an EAV table with some constraints. Attributes are foreign keys and some values are also from other tables (not realized as foreign key, but as a CHECK constraint). On this ...
0
votes
1answer
60 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), ...
1
vote
2answers
1k 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 (...
2
votes
1answer
48 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 ...
8
votes
3answers
6k 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 ...
0
votes
1answer
189 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
85 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
96 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 ...
20
votes
2answers
37k views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
2
votes
1answer
167 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
115 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
182 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 &...
0
votes
2answers
240 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 (...
0
votes
1answer
648 views

Create Table with Int-attribute which can only use values between 6 and 36

Hello I'm learning for my databases examination and in one exercise they asking for creating a table with a attribute which can only use values between 6 and 36. How do I solve it? We used in the ...
2
votes
1answer
351 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
47 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 ...
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 [...
3
votes
1answer
49 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
124 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 ...
5
votes
2answers
642 views

What are sensible/rational character limits in a database e.g. for a persons name

This is a general question about database admin. Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for names,...
4
votes
2answers
225 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
159 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 ...
0
votes
1answer
33 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
2answers
883 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
252 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, ...
5
votes
1answer
2k views

Postgresql partitioned table timestamptz constraint problem

The table reports is partitioned table by day like reports_20170414,reports_20170415 Constraint SQL is defined as follow CHECK ( rpt_datetime >= '2017-04-14 00:00:00+00'::timestamp with time ...
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
135 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
41 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. ...
3
votes
1answer
7k views

Check constraint references another column in same table

I have this table but check constraint is throwing me an error: Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'. I've seen this as allowed ...
3
votes
1answer
2k 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
654 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 ...
2
votes
1answer
359 views

Check Constraints on SQL Server Database advisable for number range & year & month?

I'm busy designing a website and I have a requirement for the following columns in my SQL Server database: allocation (values from 0 to 100) year (only) month (only) I have read about check ...
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 ...
1
vote
1answer
476 views

Implement check constraint for varchar (7) to enforce YYYY-MM

I am considering the following with regards to implementing a check constraint on a column I am calling year_month. My objective is to have a check constraint to enforce YYYY-MM. My intention is to ...
1
vote
1answer
518 views

Why does adding a simple CHECK constraint take so long on Postgres

I'm using PostgreSQL 9.6.1 on Amazon RDS I'm adding a CHECK constraint on an already indexed column named timestamp_ of a table called event_click that contains about 1 million rows. ALTER TABLE ...
3
votes
3answers
2k views

SQLite, ASCII A-Z Check Constraint

I have the following table which I'm trying to limit the column "prefix" to ASCII alphabetical characters. However, I am still able to insert other characters after using the following constraint. Why ...
17
votes
1answer
2k views

Postgres: How is SET NOT NULL “more efficient” than CHECK constraint

In PostgreSQL docs for Constraints, it says A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit ...
1
vote
2answers
2k views

List of values accepted by a column

Table Animal has following columns: SR_ID First_Name Last_Name PET The PET column has a check constraint; hence it only accepts three values DOG, CAT, FISH. Sample Data +-------+-------------+----...
0
votes
2answers
716 views

Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL)

Loan table associated to due table. also Invoice table associated to due table. In due table either one of them foreign key should present and other one should be null. What kind of constraint ...
4
votes
2answers
127 views

Creating range for special fields in SQL Server

I have two columns that look like this: Start_Post End_Post ---------- -------- 102+20.45 153+19.22 120+21.25 220+25.30 ... ... And I want to introduce a constraint to each column: ...
4
votes
1answer
1k views

Sqlite Create Table with Check column from other table

I have two tables: position CREATE TABLE IF NOT EXISTS position( id_position INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, min REAL NOT NULL CHECK (min > 0), max REAL CHECK (max &...
3
votes
2answers
667 views

Can a PRIMARY KEY constraint (with a NONCLUSTERED INDEX) check for only certain values?

Can I set a (logical) PRIMARY KEY constraint, served by a (physical) NONCLUSTERED INDEX, so that it only checks certain values of the constrained and indexed column or another column in the relevant ...