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.
76
questions
3
votes
1answer
83 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
152 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 ...
0
votes
0answers
19 views
Relationship constrains in EER
When designing a EER diagram, how do you state or show a specific relationship constraint between entities (or can this even be done).
We have a relationship between two entities E and F, but for a ...
2
votes
2answers
74 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
46 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
88 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
92 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
147 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
29 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
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
54 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 ...
3
votes
2answers
116 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
143 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
752 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
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
1answer
117 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
28 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
91 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
35 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. ...
4
votes
2answers
3k views
Constraint - 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 ...
1
vote
1answer
1k 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
482 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
742 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' );
...
1
vote
1answer
226 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 ...
4
votes
1answer
1k 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
1k 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
378 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 ...
2
votes
1answer
291 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 ...
1
vote
1answer
335 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 ...
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 ...
1
vote
2answers
1k 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
581 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 ...
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 ...
4
votes
2answers
125 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 &...
14
votes
1answer
1k 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 ...
3
votes
1answer
662 views
Check constraint on nullable data, how to approach?
This is one of the tables in my database. Constraints omitted for clarity.
CREATE TABLE [Person].[Person]
(
[ID] INT NOT NULL IDENTITY,
[Forename] ...
5
votes
1answer
1k views
In SQL server is a check constraint applied before or after a default constraint on insert?
As per the title: In SQL server is a check constraint applied before or after a default constraint on insert?
So taking the following table (Unnecessary columns omitted for brevity)...
CREATE TABLE [...
3
votes
2answers
525 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 ...
0
votes
1answer
119 views
Query For All Constraints in a SQL Database Diagram
Is there a way to show constraints in a SQL Server db diagram?
Like for example, in a card number, it shows there is a CHECK constraint etc..
0
votes
1answer
287 views
How do triggers and check constraints relate to maitaining database rules
I was looking at a tutorial on triggers for SQLite. The tutorial said that a use of triggers could be for data validation.
The following code was given as an example:
CREATE TRIGGER ...
0
votes
0answers
28 views
Constraint with respect to existing data
Talking about SQL Server.
Table contains column A and B. For simplicity - no key. Both columns may contain any values. But with a limitation: if column A receives a value which was already used in ...
0
votes
1answer
108 views
CHECK constraint not implemented in MySQL, what about other DBMS? [closed]
MariaDB, which is based on MySQL, does not.
Oracle, including Oracle XE 10g and 11g do.
I'm curious as to other DBMS?
1
vote
1answer
93 views
how to get any record in any table that would violate a foreign key constraint that would prevent my delete?
basically I am looking for something like:
before you can delete record x from my_table you need to:
1) delete x,y and z from table1
2) delete m and n from table2
3) delete a,b,c from table3
I ...
0
votes
1answer
124 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
56 views
MySQL Schema Design: One Possible Values Between Multiple Columns
I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables:
...
0
votes
1answer
183 views
Mysql columns mutual exclusion
I want to create a table which has 2 columns that would only allow one of them to have value in the same row.
I tried with CHECK constraint but it not working correctly
Ex: SQL Fiddle schema ...
1
vote
0answers
421 views
How to create a check between two attributes of two different tables
I have to create a check to know if the date in Livraison (Delivery in French) is >= Date in Commande (Order in French)
I Hhad some issues on the SQL :
We did it in BonDeLivraison (DeliveryTicket in ...
3
votes
1answer
829 views
Constraint on row value based on values in other rows
Is it possible to have a table with four columns:
CREATE TABLE accrual (
add_date date NOT NULL,
user_id integer NOT NULL,
rate integer NOT NULL,
amount numeric(7,3)
);
and then ...
1
vote
0answers
162 views
pg_restore slow after adding a check function
I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records.
The way I did this was to remove the foreign key on some tables in ...
