Linked Questions

33 votes
2 answers
17k views

Custom unique constraint, only enforced if one column has a specific value

Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is 'true', then I want the ...
Faheem Mitha's user avatar
  • 1,039
31 votes
2 answers
31k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
Shaun McCready's user avatar
6 votes
1 answer
8k views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
Jim Stewart's user avatar
9 votes
3 answers
2k views

Is it reasonable to mark all columns but one as primary key?

I have a table representing movies. The fields are: id (PK), title, genre, runtime, released_in, tags, origin, downloads. My database cannot be polluted by duplicated rows, so I want to enforce ...
rubik's user avatar
  • 525
4 votes
1 answer
9k views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
user4150760's user avatar
  • 1,099
4 votes
1 answer
4k views

Complex constraint across all data in a table

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or ...
KayEss's user avatar
  • 145
5 votes
1 answer
3k views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
vektor's user avatar
  • 401
5 votes
1 answer
5k views

Creating a partial unique constraint for MySQL

I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL. My ...
linqu's user avatar
  • 183
3 votes
1 answer
3k views

Restrict two specific column values from existing at the same time

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed. I would appreciate any help creating a constraint that will enforce this. CREATE TABLE example ( ...
Nathan Lippi's user avatar
2 votes
1 answer
3k views

Constraint on multiple columns?

I have a table edges, which describes relations in a graph: CREATE TABLE IF NOT EXISTS edges ( src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE ,tgt INT NOT NULL ...
Victoria Stuart's user avatar
4 votes
1 answer
442 views

Can column uniqueness be commutative?

I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be ...
user4150760's user avatar
  • 1,099
3 votes
2 answers
409 views

Enforce uniqueness across optional attributes for an entity

I have an entity that has multiple attributes, and these attributes are themselves entities. No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. ...
Courtney Miles's user avatar
0 votes
1 answer
722 views

Composite index design where only a subset will be queried

I have a table with columns A (typedate), B1 ... B24 (all boolean). Generally, A will always be used in every query and only one of B1 ... B24. A large subset of the B columns (more than half) will ...
Isaac's user avatar
  • 115
0 votes
1 answer
685 views

Get result from select exists, execute insert query based on the response of it

I have to insert unique value for a row but has to put check on 3 columns collectively ((unique value(col1 && col2 && col3) == true) I found a solution here it is query = select ...
Lovika's user avatar
  • 1