Skip to main content
Share Your Experience: Take the 2024 Developer Survey
55 votes
Accepted

What is a WITH CHECK CHECK CONSTRAINT?

The MSDN documentattion page about ALTER TABLE explains these: ALTER TABLE: modify the table's structure (and some of the possible actions/modifications are): CHECK CONSTRAINT ..: enable the ...
ypercubeᵀᴹ's user avatar
50 votes
Accepted

Not using foreign key constraints in real practice. Is it OK?

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits. The point of a foreign key (FK) is to ensure that this ...
Michael Green's user avatar
46 votes
Accepted

How do I enforce a write-once then read only database table in SQL?

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and ...
mustaccio's user avatar
  • 26k
35 votes

How to add a Default constraint while creating a table? SQL Server

You can name the constraint inline: CREATE TABLE tblTest( -- -- Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3, -- ) ; As the CREATE TABLE msdn page shows: DEFAULT ... To maintain ...
ypercubeᵀᴹ's user avatar
34 votes
Accepted

NOT NULL if value is true in another column

This is a rather simple CHECK constraint: CREATE TABLE my_table ( id serial, attribute boolean, number integer, CONSTRAINT if_attribute_then_number_is_not_null CHECK ( (NOT attribute) OR (number ...
ypercubeᵀᴹ's user avatar
30 votes

MATCH FULL vs MATCH SIMPLE in foreign key constraints

FULL vs SIMPLE vs PARTIAL While the chosen answer is correct, if this is new to you, you may want to see it with code -- I think it's easier to grok that way. -- one row with (1,1) CREATE TABLE foo (...
Evan Carroll's user avatar
  • 63.5k
29 votes

Constraint - one boolean row is true, all other rows false

Variant 1 Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it: CREATE TABLE ...
Erwin Brandstetter's user avatar
21 votes

Constraint - one boolean row is true, all other rows false

You can use a Filtered index create table test ( id int primary key, foo bool ); CREATE UNIQUE INDEX only_one_row_with_column_true_uix ON test (foo) WHERE (foo); --> where foo is ...
McNets's user avatar
  • 23.8k
18 votes

How to add a check constraint in Postgres without locking the table?

(Not enough rep to comment on the other answer) It's not clear from the phrasing in the accepted answer what "when a lock on the table is ok" means. According to Postgres ALTER TABLE docs: ...
Dan Anderson's user avatar
18 votes
Accepted

Is it possible to set a composite NOT NULL constraint in PostgreSQL

Let's assume the structure of your table is this one: CREATE TABLE log_logins ( user_id INTEGER NOT NULL, login_time TIMESTAMP NOT NULL DEFAULT now(), ip_v4 TEXT /* or any other ...
joanolo's user avatar
  • 13.5k
17 votes

Why does Postgres generate an already used PK value?

You are most likely tying to insert a row in a table for which the serial column sequence value is not updated. Consider following column in your table which is primary key defined by Django ORM for ...
Abhishek's user avatar
  • 181
17 votes
Accepted

Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?

My limited testing shows that the "IS NOT NULL" predicate can be eliminated if: the column is declared as NOT NULL in the table definition, or the column is protected from nulls by an ...
Josh Darnell's user avatar
  • 29.3k
16 votes
Accepted

CHECK constraint to enforce ###.###.#### pattern match

The regex in your question is not entirely unambiguous In most flavors that support Unicode, \d includes all digits from all scripts. Notable exceptions are Java, JavaScript, and PCRE. These ...
Martin Smith's user avatar
  • 84.8k
16 votes
Accepted

Can SQL Server create collisions in system generated constraint names?

Can SQL Server create collisions in system generated constraint names? This depends on the type of constraint and version of SQL Server. CREATE TABLE T1 ( A INT PRIMARY KEY CHECK (A > 0), B INT ...
Martin Smith's user avatar
  • 84.8k
15 votes
Accepted

Why do composite foreign keys need a separate unique constraint?

It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a UNIQUE constraint on (a1, a2), we can't add a FOREIGN ...
ypercubeᵀᴹ's user avatar
15 votes
Accepted

MySQL - Delete row that has a foreign key constraint which reference to itself

Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider: Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I ...
ypercubeᵀᴹ's user avatar
15 votes
Accepted

Set column to only accept negative values

CREATE TABLE t ( n INTEGER NOT NULL CHECK (n < 0) ); works in most RDBMS I know. Edit: A comment by @IMSop prompts me to specify why I wrote “most RDMS I know:” it is well known (and very ...
Dario's user avatar
  • 748
14 votes

When should I use a unique constraint instead of a unique index?

To quote MSDN as an authoritative source: There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation ...
tinonetic's user avatar
  • 240
12 votes

How to add a Default constraint while creating a table? SQL Server

Your comments on the other two answers claim that you cannot name a default constraint when creating it "inline". Both answers show that you can, in fact, provide a name for the constraint when ...
Hannah Vernon's user avatar
  • 70.1k
12 votes
Accepted

Postgres: Read CONSTRAINTS definition

This solve my problem SELECT conrelid::regclass AS table_from ,conname ,pg_get_constraintdef(c.oid) FROM pg_constraint c JOIN pg_namespace n ON n.oid = c.connamespace WHERE ...
Ephra's user avatar
  • 141
11 votes

MySQL - Delete row that has a foreign key constraint which reference to itself

SET FOREIGN_KEY_CHECKS=0; DELETE FROM forum WHERE Owner_Id = 1 ORDER BY nright; SET FOREIGN_KEY_CHECKS=1; just not forget in this case You must manually parse situations when parent_id show to 1, ...
a_vlad's user avatar
  • 3,655
11 votes
Accepted

In SQL server is a check constraint applied before or after a default constraint on insert?

The check constraint is applied after. You can see this from the fact that the following succeeds. DECLARE @T TABLE(X INT DEFAULT 1 CHECK (X = 1)); INSERT @T DEFAULT VALUES; Also the execution ...
Martin Smith's user avatar
  • 84.8k
11 votes

How to check if there are no time gaps in PostgreSQL?

Chapter 1: Linked List One way to have this (no gaps) type of constraint enforced in the database is to split activity into the starting and ending parts and then use UNIQUE and FOREIGN KEY ...
ypercubeᵀᴹ's user avatar
11 votes
Accepted

Partially-Unique Check Constraints

You can use a filtered unique index for this: CREATE UNIQUE INDEX LinkedID_Unique_except_Zeroes_or_Null ON dbo.oldAccessTable (LinkedID) WHERE LinkedID <> '00000000' ; The ...
ypercubeᵀᴹ's user avatar
11 votes

How do I enforce a write-once then read only database table in SQL?

Permissions seem to to be the obvious choice - however you could also use the ARCHIVE Storage Engine. This table engine is designed to record large amounts of data that will not change: The ARCHIVE ...
iHaveacomputer's user avatar
11 votes

Can SQL Server create collisions in system generated constraint names?

Assuming I have 100 million tables, I calculate less than a 1-in-1-trillion chance of a collision Remember this is the "birthday problem". You're not trying to generate a collision for a single ...
David Browne - Microsoft's user avatar
11 votes

SQLServer add a default value for a column

It's your quotes. If you remove those, and change your literal to use single quotes, you should be fine. SQL Servers DEFAULT is SET QUOTED_IDENTIFIER ON and with this literals must be delimited by ...
S3S's user avatar
  • 3,578
11 votes
Accepted

When exactly are IMMEDIATE constraints checked and what exactly is a "statement" in Postgres?

As mentioned in a comment, a statement is everything between the previous statement terminator (usually a semicolon) and the next one, so this is one statement: with t1 as (insert into b values (100, ...
10 votes

Mutually exclusive many-to-many relationships

There is a way to implement this declaratively only without changing your current setup much, if you agree to introduce some redundancy to it. What follows can be considered a development on RDFozz's ...
Andriy M's user avatar
  • 23k
10 votes
Accepted

PostgreSQL - Enforcing unique constraint on date column parts

Using EXTRACT(year/month ...) works, too: create unique index year_month_uq on foo ( extract(year from mydate), extract(month from mydate) ) ;
ypercubeᵀᴹ's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible