Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

SQL allows for a column to have a CHECK constraint defined. This, quite literally, checks the value about to be written to that column. The constraint is defined as a predicate which evaluates to TRUE, FALSE or UNKNOWN. When the predicate evaluates to TRUE or UNKNOWN the value will be written; evaluation to FALSE will cause the write to fail.

Typical use is to limit a column's domain further than the data type alone can do. For example, column WEIGHT may be defined as an integer. It should not contain negative values, however. This could be enforced by a CHECK constraint

create table ..
(
    weight int CONSTRAINT positive_weight CHECK (weight > 0)
...
)

Attempts to write a WEIGHT of zero or less will now fail.

112 questions
32
votes
2 answers

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 NOCHECK ADD CONSTRAINT [FK_T2_T1] FOREIGN…
Delux
  • 694
  • 1
  • 7
  • 14
24
votes
3 answers

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 both columns. It's either or but one is a must.
Kevin
  • 387
  • 1
  • 3
  • 7
23
votes
2 answers

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 dumpfile My problem is that the database contains…
Marcus Junius Brutus
  • 2,869
  • 6
  • 26
  • 40
22
votes
1 answer

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 not-null constraint is more efficient. I'm…
Robin Joseph
  • 321
  • 2
  • 6
13
votes
1 answer

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} Start of string A single character present in the…
Martin Smith
  • 77,689
  • 15
  • 224
  • 316
9
votes
5 answers

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 CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK…
user83914
7
votes
2 answers

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 table words_table ( id serial primary key, …
Alex Fruzenshtein
  • 231
  • 1
  • 3
  • 8
7
votes
1 answer

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 additional constraint in DB that says Item should…
Nishant
  • 737
  • 2
  • 9
  • 20
6
votes
1 answer

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, amount BIGINT, . . . ., refunded…
Kinnard Hockenhull
  • 165
  • 1
  • 5
6
votes
1 answer

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". But, when you join that table to another that does not have a…
Anil Raghavan
  • 63
  • 4
6
votes
1 answer

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 either null or defaulted to "00000000". However,…
Sean K.
  • 188
  • 1
  • 2
  • 9
5
votes
2 answers

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, phone number etc where you know the sort of data…
Coops
  • 201
  • 2
  • 5
5
votes
1 answer

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 [FK_MainTable_ForeignKeyTable] FOREIGN…
mathewb
  • 1,074
  • 1
  • 8
  • 23
5
votes
1 answer

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 zone AND rpt_datetime < '2017-04-15…
onesvat
  • 127
  • 1
  • 6
5
votes
1 answer

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 > 0), ); min, max links to salary employee CREATE…
Kuba Ronkiewicz
  • 97
  • 6
1
2 3 4 5 6 7 8