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
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 ...
21
votes
2answers
38k 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 ...
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 ...
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 ...
7
votes
5answers
4k views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
6
votes
1answer
661 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 ...
6
votes
1answer
6k views

Change constraint on column based on value of another

Is it possible to change a constraint on a column in postgres based on the value of another column? E.g. (pseudocode): CREATE TABLE transactions( id SERIAL PRIMARY KEY, type TXN_TYPE NOT NULL,...
6
votes
1answer
819 views

Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order. How to impose an ...
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 ...
5
votes
1answer
2k 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 [...
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
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 ...
5
votes
2answers
643 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
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
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",...
4
votes
1answer
3k views

Check constraint for Canadian postal code?

Is there a way to check if a Canadian postal code is valid? Not sure if something like this would work: PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9'
4
votes
1answer
2k 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] ...
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 &...
4
votes
2answers
232 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 ...
4
votes
1answer
1k 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 ...
4
votes
1answer
683 views

Surrogate vs composite key in hierarchical data structure

I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The ...
3
votes
2answers
668 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 ...
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 ...
3
votes
2answers
13k views

Check Constraint with Case Statement

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of ...
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
2answers
4k views

PostgreSQL CHECK constraint is evaluated wrongly

Following schema allows to violate the CHECK constrait, but only once. Then the constraint is correctly enforced. drop database if exists example; create database example; create table example_table ...
3
votes
1answer
101 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 ...
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 ...
3
votes
1answer
2k views

Table structure for validating data using category and subcategory fields

I am creating a simple budget application, and the primary table will be a transaction table that contains things like the amount of the transaction, date, account, the category and subcategory (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 -- + ----- + ---- + ----- + ---- ...
2
votes
4answers
6k views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
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
1answer
360 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 ...
2
votes
2answers
113 views

Does any SQL engine have navigation support in CHECK clauses?

Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date ...
2
votes
2answers
117 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 ...
2
votes
1answer
360 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
votes
1answer
483 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
2
votes
1answer
51 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 ...
2
votes
0answers
468 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ (filetable_parents_are_directories((...
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 (...
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. ...
1
vote
1answer
481 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
483 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 ...
1
vote
1answer
47 views

Can I enforce one field being dependent on another?

I would like to know if it's possible to have two fields (in this case bits) in the same table where one is dependent on another. For example Check2 may only be true if Check1 is true. Note: I'm ...
1
vote
1answer
185 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
1answer
529 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 ...
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 +-------+-------------+----...
1
vote
1answer
170 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 ...
1
vote
2answers
7k views

Compare Indexes/Constraints between two SQL Server databases

I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. ...
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 ...