All Questions
31 questions
3
votes
1
answer
89
views
Enforce unique constraint without denormalization
Option 1
I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD
A Task belongs to a Service (like in ECS)
A Task can be reassigned to another Service.
TaskConfig tracks ...
- 133
1
vote
2
answers
72
views
How to enforce entity existence across N-tables - postgres
Let's say we decided to split user table in two, one will have data related to authentication, another basic user description:
user_table
user_id | name
1 | Max
2 | Alex
3 | ...
- 135
6
votes
2
answers
939
views
Make two columns unique both ways
I'm creating a database which stores football matches.
Each round, any given football club may only play once (home or away).
For example, after the insert of
INSERT INTO Match (Round, Home, Away) ...
- 63
4
votes
1
answer
771
views
UPSERT based on unique combination of (INTEGER, NULL), (NULL, INTEGER)
My table, for simplicity, looks like this:
id | foreign_key_1 | foreign_key_2 | value
As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables.
The tricky ...
- 209
3
votes
2
answers
990
views
Implement foreign key constraint when there are several parent tables
It might be the case that my initial design is flawed, but let's start from there and see whether there is a good enough approach.
I have two entities, say, A and B that are very similar but not ...
- 209
2
votes
1
answer
183
views
How to enforce UNIQUE constraint for sixth normal form in PostgreSQL
I have a table person which I am planing to normalize to the sixth normal form.
The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the ...
6
votes
1
answer
10k
views
Column constraint based on values in another column
I have a PostgreSQL table phase_steps, with the following example rows:
phase_step_id|step_type|step_status|
-------------+---------+-----------+
1| RESEARCH| |
2| ...
- 167
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 ...
- 181
0
votes
1
answer
78
views
Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table
I'm using a PostgreSQL table to link taxonomic names with the respective taxon concept as follows:
create type name_status as enum ('accepted','basionym','synonym');
create table temp (
concept_id ...
- 101
39
votes
2
answers
43k
views
Constraint - one boolean row is true, all other rows false
I have a column: standard BOOLEAN NOT NULL
I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with ...
- 491
1
vote
1
answer
808
views
Composite constraint of numrange and boolean
I have a current constraint like this:
ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH &&...
- 169
2
votes
2
answers
233
views
Unique constraint on different columns
I'm trying to make a table to store the edges of a directed graph, but want to avoid duplicate edges in both directions.
I can of course make it one direction :
CREATE TABLE edge (parent integer, ...
- 23
9
votes
1
answer
1k
views
How to check if there are no time gaps in PostgreSQL?
Schema:
CREATE TABLE "expenses_commissionrule" (
"id" serial NOT NULL PRIMARY KEY,
"country" varchar(2) NOT NULL,
"created" timestamp with time zone NOT NULL,
"modified" timestamp ...
- 475
9
votes
5
answers
3k
views
Mutually exclusive many-to-many relationships
I have a table containers that can have a many-to-many relationships to several tables, let's say those are plants, animals and bacteria. Each container can contain an arbitrary number of plants, ...
- 1,250
4
votes
1
answer
1k
views
Best database design for payments with multiple constraints
I have to register payments.
There are two things to pay. thingC or thingD (they may not be paid).
You can only pay one thingD per payment transaction.
You can pay many thingC per payment transaction....
- 255
4
votes
1
answer
930
views
How to enforce that a parent must have at least one child when the PK is composite?
Consider the following business domain:
An Airline has a unique airline id (aid) and contains zero-or-more Planes.
A Plane has a unique plane id (pid) in the Airline it flies for (but planes from ...
- 145
3
votes
1
answer
107
views
enforcing data integrity for sequential relations
These are my tables:
create table trips (
trip_id serial primary key,
trip_nm text
);
create table trip_segments (
segment_id serial primary key,
departure_ts timestamp with time zone, -- ...
- 153
8
votes
1
answer
2k
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 ...
- 899
3
votes
1
answer
11k
views
Cascade deletes from multiple tables
I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
- 33
8
votes
2
answers
6k
views
CREATE as SELECT with CONSTRAINT?
I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null.
I cannot find valid SQL for this ...
- 203
0
votes
1
answer
52
views
Assign value to a field after checking a condition
I have this query:
ALTER TABLE MERCHANTS
ADD COLUMN merchant_image bytea,
ADD COLUMN merchant_logo bytea,
ADD COLUMN merchant_address VARCHAR(100),
ADD COLUMN merchant_phone VARCHAR(50),
...
- 101
17
votes
2
answers
20k
views
Conditional Foreign Key Relationship
I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
- 273
32
votes
3
answers
15k
views
Constraint to enforce "at least one" or "exactly one" in a database
Say we have users and each user can have multiple email addresses
CREATE TABLE emails (
user_id integer,
email_address text,
is_active boolean
)
Some sample rows
user_id | email_address | ...
- 825
6
votes
1
answer
1k
views
Multi-tenant database constraints
I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other.
My question is, is there any sort of way that I can use ...
- 433
5
votes
2
answers
7k
views
Unique across tables
I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users).
However, each user can also have additional emails.
...
- 655
0
votes
1
answer
192
views
What does a CONSTRAINT have to do with my unique index?
I have to indexes on my table. The first was created by Django and the second by me. I'm not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change ...
- 355
6
votes
1
answer
4k
views
How to enforce an "exactly-one-per-group" constraint?
I have a table x that I've defined like this:
CREATE TABLE x (
xid INTEGER NOT NULL PRIMARY KEY,
yid INTEGER NOT NULL REFERENCES y(yid),
is_principal BOOLEAN NOT NULL
);
This definition ...
- 341
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
(
...
- 133
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 ...
- 163
2
votes
1
answer
2k
views
PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'.
Various revisions of this SQL isn't constraining '-/+infinity' input:
CREATE TABLE bill
(
id ...
- 205
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 ...
- 1,039