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.

131 questions
39
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
  • 774
  • 1
  • 7
  • 14
30
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
  • 447
  • 2
  • 5
  • 7
29
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
  • 3,409
  • 7
  • 30
  • 44
24
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
  • 341
  • 2
  • 6
14
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
  • 87,941
  • 15
  • 255
  • 354
9
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
  • 311
  • 1
  • 3
  • 9
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
8
votes
3 answers

How do I add a trusted check constraint quickly

I'm adding a check constraint to a large table because I want to prepare to switch it into a partitioned table using partition switching. The check is a simple inequality check on a column and there is an index on that column. But when I add the…
Michael J Swart
  • 2,235
  • 5
  • 23
  • 32
8
votes
1 answer

Why won't this UDF work in this check constraint?

I’m trying to use a UDF as an alternative to using a SELECT command within a check constraint. I need to put a constraint on the report table to ensure the signedBy field is the PK of a personnel record for which there exists a record in the auth…
agerber85
  • 121
  • 5
8
votes
3 answers

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 is it not working? CREATE TABLE test ( id…
z64
  • 81
  • 1
  • 2
8
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
  • 899
  • 2
  • 13
  • 20
7
votes
1 answer

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 Brandur responds, Excellent question! (1) VARCHAR and…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
7
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,152
  • 1
  • 9
  • 24
7
votes
1 answer

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] VARCHAR(16) NOT NULL, [Surname] …
Jake
  • 279
  • 2
  • 3
  • 7
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
  • 2
  • 5
1
2 3
…
8 9