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 plpgsql, but this seems like a sledgehammer. I would prefer something like a CHECK or UNIQUE constraint. The simpler the better. One row must be True, they cannot all be False (so the first row inserted would have to be True). The row will need to be updated, which means I have to wait to check constraints until updates are done, since all rows may be set False first and one row True afterwards. There is a FK between products.tax_rate_id and tax_rate.id, but it has nothing to do with the default or standard tax rate, which is user selectable to ease creating new products.. PostgreSQL 9.5 if it matters. ### Background The table is the tax rate. One of the tax rates is the default (standard since default is a Postgres command). When a new product is added, the standard tax rate is applied to the product. If there is no standard, the database must either do a guess or all kinds of unneeded checks. The simple solution, I thought, was to make sure there is a standard. By "default" above, I mean for the presentation layer (UI). There is a user option for changing the default tax rate. I either need to add extra checks to ensure the GUI/user does not try to set the tax_rate_id to NULL, or then just set a default tax rate.

I am trying to create a scheduling application, and part of it requires the date range of a row to be within the date range of a row in another table, which it is referencing via a foreign key. Note: This is Postgres. The schema is as follows:

table_1:
id Serial
name Varchar
date_range DATERANGE

table_2:
id Serial
name Varchar
date_range DATERANGE
table_1_id - Foreign Key, this references the id of a record in table 1.

I am trying to figure out how to write a check constraint that enforces the rule that the date_range in table_2 has to be within the date_range of table_1.

## Problem statement I have a table with a column whose values are foreign keys, but the target table of the foreign key differs from row to row. The relevant table can be determined from the key value alone, and there is a small, fixed set of such tables. I'd like to add a foreign key constraint here so that my DBMS can ensure referential integrity. Of course, I can't do this directly, but I have a proposed solution that involves an intermediate "forwarding table" with incoming and outgoing foreign key constraints. I'm looking for review on: - whether this solution in fact solves the problem, or if I missed an edge case; - how this solution may fare in the face of changes to the data model (e.g., new referent tables); - whether this use of Postgres GENERATED ALWAYS AS ... STORED columns is reasonable or suspect; - whether this solution is likely to introduce concurrency issues. ## Proposed solution To illustrate the solution, consider a simple database that stores "users" and "groups". Users and groups are each keyed by integer IDs, and some bits of the ID are reserved to tell what kind of ID it is:

-- User and group IDs are both integers, but are in disjoint subsets of the key
-- space, distinguished by the low 8 bits.
CREATE DOMAIN userid AS int8 CHECK ((VALUE & 255) = 1);
CREATE DOMAIN groupid AS int8 CHECK ((VALUE & 255) = 2);

CREATE TABLE users(
    user_id userid PRIMARY KEY,
    name text NOT NULL
);
CREATE TABLE groups(
    group_id groupid PRIMARY KEY,
    admin userid NOT NULL REFERENCES users
);

INSERT INTO users(user_id, name) VALUES (1, 'alice'), (257, 'bob');
INSERT INTO groups(group_id, admin) VALUES (2, 1), (258, 1);

Now, both users and groups can create invoices. Invoices have entirely the same data whether they're created by a user or a group, so we just use a single table that stores the ID of the "actor" (user or group) that created the invoice along with the extra data:

-- Invoices can be created by either users or groups: collectively, "actors".
CREATE DOMAIN actorid AS int8 CHECK ((VALUE & 255) IN (1, 2));
CREATE TABLE invoices(
    actor actorid NOT NULL,
    create_time timestamptz NOT NULL,
    amount_cents int NOT NULL
);

Now, semantically, invoices.actor is a foreign key onto *either* users or groups, depending on the value of actor & 255. There's no way to directly write a REFERENCES constraint for that. We can imagine defining a view of all the actor IDs—

CREATE VIEW all_actor_ids AS (
    SELECT user_id AS actor FROM users
    UNION ALL
    SELECT group_id AS actor FROM groups
);

—such that, in principle, actor actorid REFERENCES all_actor_ids, but Postgres does not actually allow referring to views in foreign keys . To work around this, we basically materialize all_actor_ids into a table that itself has foreign key constraints to ensure its own integrity:

CREATE TABLE actors(
    actor actorid PRIMARY KEY,
    user_id userid
        REFERENCES users
        GENERATED ALWAYS AS (CASE WHEN (actor & 255) = 1 THEN actor END) STORED,
    group_id groupid
        REFERENCES groups
        GENERATED ALWAYS AS (CASE WHEN (actor & 255) = 2 THEN actor END) STORED,
    CONSTRAINT actors_exactly_one_key
        CHECK (1 = (user_id IS NOT NULL)::int + (group_id IS NOT NULL)::int)
);

Now, invoices.actor can refer to actors:

ALTER TABLE invoices ADD FOREIGN KEY (actor) REFERENCES actors;

The idea is that, before you add an invoice on behalf of an actor, you first run INSERT INTO actors(actor) VALUES($1) ON CONFLICT DO NOTHING. The generated columns take care of populating either user_id xor group_id, the foreign key constraints on those columns ensure that the underlying entity actually exists, and the conflict handler makes the operation a no-op if the actor has been used before. For example, with the above definitions, these inserts work:

-- All users and groups can be populated as actors.
INSERT INTO actors(actor)
    SELECT user_id FROM users UNION ALL SELECT group_id FROM groups
    ON CONFLICT DO NOTHING;

-- Invoices can be created for either actors or groups.
INSERT INTO invoices(actor, create_time, amount_cents)
    VALUES (1, now(), 100), (258, now(), 200);

Note that the actors data never actually needs to be part of a JOIN in a read path. It exists only to coax the foreign key constraints into submission. ## Questions It seems to me that this solution should properly ensure referential integrity: in particular, a user or group can't be deleted without cascading down to delete any invoices created by that user or group. But I have some questions: - Am I missing some edge case in which this solution does not actually ensure referential integrity? - Suppose that invoices can now also be created by a third type of entity: say, robots. I think that I can alter the actorid domain to incorporate robotids, then add a new actors.robot_id column like the others and update the actors_exactly_one_key constraint. Are there lurking issues that I should be wary of here? - I haven't used Postgres GENERATED ALWAYS AS ... STORED columns before, and I'm a little nervous that the default expression can't be changed at all after the fact. Does this seem like an appropriate use of generated columns, or would it be better to replace the generated columns with CHECK constraints that ensure the same values but require the user to provide them? - Is the INSERT INTO actors(actor) ... ON CONFLICT DO NOTHING likely to introduce concurrency issues? (Or, are there any other glaring performance issues that I've missed?) Any other feedback or reviews also warmly appreciated. I'm using Postgres 12, but if the best solution here requires upgrading to Postgres 14, I'm open to it.

Table B have several thousand records that references table A with a foreign key. The application sends a request to delete the row from table A. It's important that the synchronous operation would be immediate and won't be at risk for timeouts. If a cascade delete is used, and thus several thousands of records will be deleted as well, could it cause the deletion to take a long time? (not immediate). Assuming that it could lead to a long deletion operation, what could be done as an alternative? I've though perhaps to delete the record as deleted and to perform the actual deletion in some background process (where the referencing rows would be deleted before the parent), but this approach feels rather error prone (since all of the existing queries will need to reference this new markedAsDeleted column. Any ideas?

I am drawn to this design like a moth to a flame. I've read rumblings that cyclic foreign keys are a nightmare. Generally, I can see why they should be avoided. In this particular case, however, I don't see why it would be so awful. Got some inspiration from this article, but combined the reverse-belongs-to and exclusive-belongs-to designs into one... monstrous creation? Can you tell me why (or if) this is a bad idea? Like what headaches am I going to get into with this and are they worth it? Anyway, I want to create a polymorphic relationship between many entities and one table. I want to make something like this: -- poly ids are uuids and we assume they will not collide, period create domain poly_id uuid; create table foo ([...table columns], poly_id not null default gen_random_uuid()) create table bar ([...table columns], poly_id not null default gen_random_uuid()) create table baz ([...table columns], poly_id not null default gen_random_uuid()) create type poly_t as enum ('foo', 'bar', 'baz') create table poly_obj ( -- poly_id is always the poly_id of the one set reference column poly_id poly_id not null generated always as ( coalesce("foo", "bar", "baz") ), poly_t poly_t not null, "foo" poly_id null references foo (poly_id) check ( "foo" is null or poly_t = 'foo' ), "bar" poly_id null references bar (poly_id) check ( "bar" is null or poly_t = 'bar' ), "baz" poly_id null references baz (poly_id) check ( "baz" is null or poly_t = 'baz' ) -- only one fk to child table can be set check ( ( ("foo" is not null)::integer + ("bar" is not null)::integer + ("baz" is not null)::integer ) = 1 ) ) create unique index on poly_obj ("foo") where "foo" is not null; create unique index on poly_obj ("bar") where "bar" is not null; create unique index on poly_obj ("baz") where "baz" is not null; alter table foo add foreign key (poly_id) references poly_obj (poly_id) alter table bar add foreign key (poly_id) references poly_obj (poly_id) alter table baz add foreign key (poly_id) references poly_obj (poly_id) -- more pseudocodey than the rest create trigger ___ after insert on foo for each row execute insert into poly_obj (poly_t, foo) select ('foo', poly_id) from new; create trigger ___ after insert on bar for each row execute insert into poly_obj (poly_t, bar) select ('bar', poly_id) from new; create trigger ___ after insert on baz for each row execute insert into poly_obj (poly_t, baz) select ('baz', poly_id) from new;

I'm currently in a battle with Entity Framework over trying to make a self-referencing relationship without having to add additional columns/properties. I have confirmed the foreign key I envision is possible in SQL Server, but it is difficult to research possible solutions because I keep finding generic single-column foreign keys. Is there some widely used term specific to this form of self-referencing, composite foreign key? create table LookupValues ( LookupName nvarchar(255) not null, ExternalRefCode nvarchar(255) default N'' not null, ParentExternalRefCode nvarchar(255), constraint PK_LookupValues primary key (LookupName, ExternalRefCode) ) alter table LookupValues add constraint CompositeFK foreign key (LookupName, ParentExternalRefCode) references LookupValues The idea behind it is that although there are multiple values for LookupName in this table, there will never be cross-value relationships in the parent/child relationships. e.g. a child from LookupName: A must belong to a parent from LookupName: A. I've tried searches like "self-referencing composite foreign key", "self-referencing foreign key using part of primary key", and "composite foreign key with common column in primary key", etc.

Do any of the graph-based/graph-aware databases (Neo4j, ArangoDB, OrientDB, or other) have mechanisms for maintaining referential integrity on a par with those offered by relational databases? I'm exploring various document-based databases to find a suitable engine to use for adding an auxiliary data storage to a certain project. I discovered graph-based/multimodel databases and they seemed like a good idea, but I was surprised to find that they don't seem to offer the same level of protection of relations/links/edges that modern relational databases have. In particular, I'm talking about linking deletions of entities/vertices with deletion of links/edges. In a relational database, I can have a foreign key constraint that links records from one table with records in another table, and will either 1. prevent deletion of record in table A if it's referenced by record in table B ("on delete no action"), or 2. delete the referencing record(s) in table B if a referenced record in table A is being deleted. I expected to find a similar mechanics in graph-aware databases. For example, if a "comment" vertex links to a "post" vertex (forming a many-to-1 relation), then there are the following problems/challenges to solve: 1. Prevent deletion of a post while there are edges from comments to this post. This way, a comment could never have a dangling link/edge to a post. The solution would be: depending on the link/edge properties, either 1. prevent deletion of a post until all edges from comments to this post are deleted, or 2. delete all comments linking to this post when the post is being deleted. 2. Prevent deletion of an edge from a comment to a post without deleting the comment itself, to prevent the comment from not having a link/edge to a post at all. 3. Only allow creation of a comment if an edge is created to link this comment to a post at the same time. Are mechanisms like this really lacking in graph-based databases, or was I just unable to find them? I know that OrientDB has the "link" data type that probably solves the second and the third problem (if a link-typed property is declared mandatory and non-null, then it's impossible to create a record without specifying the link destination, and later it's impossible to break the link by un-setting the property). However, as far as I remember, it's possible to delete the record which a link-typed property points to, thus producing a dangling link (so the first problem is not solved). I also know that in certain databases I can use nested documents as an alternative to having multiple linked documents. However, this approach doesn't scale well (for cases when the number of linking records grows can grow indefinitely). Also, it is quite limited (it can't be used as an alternative when several links are needed, say, to a post and to a user; there are other important limitations, too).

I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs . The default is MATCH SIMPLE. How do they function?

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       | Should not be possible

auth_table
user_id | email   | password
1       | m@m.com | 123
2       | a@a.com | 321
4       | s@s.com | Should not be possible

Is it possible to enforce existence of the same user_id? Basically both tables should have exactly same number of rows and each row should have a match in both tables.

In MySQL, I'm working with a database that has integrity constraints on foreign keys (nothing overly special here). Is there a method to somehow visualize the effects of a cascading delete or update? I would like to somehow see all records that will be effected, the whole chain of it, across all involved tables, preferably (but not a hard requirement at all) before they happen. Is this possible? **Update**
I'm trying to estimate the impact of cascading deletes/updates, seeing the result would greatly speed up my understanding.

I have to design an ERD for a hypothetical scenario. In the scenario, the nurse at a rural clinic needs to get summary data regarding patient visits so she can reschedule canceled or no-show visits. For entities so far I have: patient, provider, and visit I'm stuck on how to model the relationship between patient and visit without either creating a lot of redundancy or entering a lot of null relationships. I was initially thinking of breaking the visit entity up into no-show, canceled, and completed with 1:1 relationships between them and the patient entity. Would this create a bunch of null entries for the entities that do not apply to the patient, or is it possible to create an inherited relationship where there is only data entered if the relationship applies (much like a dependent and employee relationship)? Thank you for any feedback/insight you can provide as I work through this.

To be clear, I mean to ask this question from a strictly ["Data Modelling and database-design, including referential-integrity"](https://dba.stackexchange.com/help/on-topic) PoV. I am currently designing a database that will serve as an aggregation service. However, our third-party data partners *will not* be providing us with data dumps. Rather, we need to be able to reference their data, and direct users to appropriate locations and queries to be able to retrieve the data they need directly. ## Here's a simple example of what I mean: Suppose that we are partnering with schools. Ecah school has information about students, teachers, courses and subjects such as those in [this diagram](https://www.databasestar.com/database-normalization/) : Our goal, in this example, is to provide a central resource for information about students and teachers and courses and subjects for the entire school district. We are not allowed to simply copy all of the data from each school, as this information is sensitive. So, we would like to be able to *reference* the data that is stored at individual schools, so that users can get the information that they need easily, directly from the source. So, in our example, suppose that we cannot have any of the Student data, but we are allowed to have all of the Subject data. Also, we know the data model as shown in the picture. Suppose that a user comes to us and asks "what students are taking the subject Calculus I?" We cannot tell them this directly, because we do not have access to the Student data. However, using, say, a Schools table, and a table that references Subjects per School, and the metadata from the Data Model, we can provide links to School portals that allow users to get the Students taking that Subject from each School. Conceivably, we could also provide queries to execute, or even provide a mechanism to execute queries to access data about all of the Students from all of the Schools, and *we* would never actually have read access to any of the Student data. Note that our data model is different from the original single-school data model in several ways. We have a School table. We have a School2Subject many-to-many relationship - so, our Subject table will need a SchoolID, for example. We also need to have data for linking to resources or for creating queries from a data model. ### The Data Modeling Question: Given a data model, and a subset of that data model that I am allowed access to, how can I go about methodically designing a referential data model that allows me to query the ontological relationships to the rest of the original data model? Is there some database modeling theory that would help me plan this? Are there algorithms that can help me design such a data model so that, for example, my referential data model can be automatically updated to reflect changes in the original data model?

Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it. quote from MySQL 5.7 Reference Manual / GRANT Syntax > REFERENCES Enable foreign key creation. Levels: Global, database, > table, column. quote from PostgreSQL 9.6 / GRANT > REFERENCES To create a foreign key constraint, it is necessary to have > this privilege on both the referencing and referenced columns. The > privilege may be granted for all columns of a table, or just specific > columns. is GRANT REFERENCES only about creating a foreign key constraint? In what business case does it make sense to forbid creating a foreign key constraint (but allow to create tables)? Can you give me real world examples?

I have two tables:

ObjectContainer
    containerID        INT NOT NULL PRIMARY KEY
    mainObjectID       INT NOT NULL FOREIGN KEY REFERENCES Object (objectID)

Object
    objectID           INT NOT NULL PRIMARY KEY
    containerID        INT NOT NULL FOREIGN KEY REFERENCES ObjectContainer (containerID)

I have a circular reference because I wanted to store the main object in an instance of ObjectContainer. Now I don't want the circular-reference anymore because I read there could be problems using a MySQL-Database. So I added a third table "MainObject".

ObjectContainer
    containerID        INT NOT NULL PRIMARY KEY

Object
    objectID           INT NOT NULL PRIMARY KEY
    containerID        INT NOT NULL FOREIGN KEY REFERENCES ObjectContainer (containerID)

MainObject
    containerID        INT NOT NULL PRIMARY KEY
    objectID           INT NOT NULL
    FOREIGN KEY (containerID, objectID) REFERENCES Object (containerID, objectID)

How can I do this in JPA? This are my current classes:

@Entity
@Table(name = "tb_objectcontainer)
public class ObjectContainer {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "c_id")
  private Integer id;
  @OneToOne
  @JoinColumn(name = "c_main_object_id", nullable = true, referencedColumnName = "c_id")
  private Object mainObject;
...
}

and

@Entity
@Table(name = "tb_object", uniqueConstraints = {
  @UniqueConstraint(columnNames = { "c_object_container_id", "c_name" }) })
public class Object {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "c_id")
  private Integer id;
  @Column(name = "c_name", nullable = false)
  private String name;
  @ManyToOne
  @JoinColumn(name = "c_object_container_id", nullable = false, referencedColumnName = "c_id")
  private ObjectContainer objectContainer;
  @ManyToOne
  @JoinColumn(name = "c_parent_id")
  private Object parent;
  @OneToMany
  private List children = new ArrayList();
...
}

I have a table of Producers and a table of Products, both of which are of the form: - Id - int, Primary key - Name - nvarchar A Producer can carry multiple Products, so I was going to create a table called ProducerDetails that would have: - ProducerId - int, Foreign key to Producers.Id - ProductId - int, Foreign key to Products.Id Would it be better database design to have an additional Id (int, Primary key) column in my ProducerDetails table? I'm using SQL Server 2008 R2. The relationship between these tables is many-to-many. A producer can carry multiple types of products, and the same product could be produced by multiple different producers.

I am keeping a database of books I've read, using the following two tables in PostgreSQL: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL PRIMARY KEY, title text, author_id integer REFERENCES authors(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE(title, author_id) ); Now when going through my list of authors, I found the following two entries: id | name ---------- 1 | Mark Twain 2 | Samuel Clemens What I'd like to do is delete the "Mark Twain" entry, and effectively update all books referencing "Mark Twain" to reference "Samuel Clemens". *I know I could do this manually, but I want a solution that works, regardless of which tables are referencing the authors(id)* I thought about doing it like this (within a transaction): 1. Change Mark Twain id to 2, letting UPDATE CASCADE take care of changing the references. 2. Delete Mark Twain entry But this runs into a few problems, mainly: 1. The first step creates a duplicate primary key 2. I'm not sure how to reference the right row to delete, once they both have the same ID! 3. The DELETE CASCADE worries me for the second step There's also a subtler problem, that can be illustrated with a portion of my (poorly curated) books table: id | title | author_id ------------------------------------ 1 | "Huckleberry Finn" | 1 2 | "Huckleberry Finn" | 2 Here, even if my two-step process succeeded, I would be violating the UNIQUE contstraint on books. Is there a way to do this, and work around most/all of these issues? Using Postgres 9.4.

I have two tables mutually referencing each other, and I get this error when trying to insert into one of them: "Foreign key constraint is incorrectly formed". I have been stuck for three weeks this assignment and can't seem to get anywhere or find someone with database experience to help. I have attached all three queries but only receive an error on the last query where I create the table "DEPARTMENTS" CREATE DATABASE k0310855_AMAZON; USE k0310855_AMAZON; my tables: CREATE TABLE EMPLOYEE ( EMP_ID int PRIMARY KEY, EMP_SALARY int, EMP_PHONE varchar(255), EMP_DEPARTMENT varchar(255) ); and: CREATE TABLE CHILDREN ( CHI_NAME int, CHI_AGE int, EMP_ID int, CONSTRAINT CHILDREN PRIMARY KEY (EMP_ID, CHI_NAME) ); and: CREATE TABLE DEPARTMENTS ( EMP_DEPARTMENT int PRIMARY KEY, DEP_NAME varchar(255), DEP_BUDGET int, DEP_MANAGER varchar(255), CONSTRAINT FOREIGN KEY (DEP_MANAGER) REFERENCES EMPLOYEE (EMP_DEPARTMENT) );** I am receiving the following error: ERROR 1005 (HY000): Can't create table k0310855_AMAZON.DEPARTMENTS (errno: 150 "Foreign key constraint is incorrectly formed")

Let's say I have the following tables describing some vehicles

CREATE TABLE class (
  id INT GENERATED ALWAYS AS IDENTITY,
  label TEXT NOT NULL,

  PRIMARY KEY (id, label),
  UNIQUE (id)
);

CREATE TABLE maintenance_frequency (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  class_id INT REFERENCES class (id),
  freq FLOAT NOT NULL
);

CREATE TABLE function (
  id INT GENERATED ALWAYS AS IDENTITY,
  class_id INT REFERENCES class (id),
  label TEXT NOT NULL,

  PRIMARY KEY (id, label),
  UNIQUE (id)
);

CREATE TABLE vehicle (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label TEXT  NOT NULL,
  class_id INT REFERENCES class (id),
  freq_id INT REFERENCES maintenance_frequency (id),
  fun_id INT REFERENCES function (id)
  
);

INSERT INTO class(label) VALUES
('car'),
('motorbike');


INSERT INTO function(class_id, label) VALUES
(1, 'leisure'),
(1, 'work'),
(2, 'beach trip');

INSERT INTO maintenance_frequency(class_id, freq) VALUES
(1, 0.5),
(1, 1.0),
(2, 1.5),
(2, 2.0);

SELECT * FROM class;
SELECT f.id, c.label as class, f.label as function FROM function as f 
  INNER JOIN class as c ON c.id = f.class_id;
SELECT mf.id, c.label as class, mf.freq FROM maintenance_frequency as mf 
  INNER JOIN class as c ON c.id = mf.class_id;

| id | label | |---:|:------| | 1 | car | | 2 | motorbike | | id | class | function | |---:|:------|:---------| | 1 | car | leisure | | 2 | car | work | | 3 | motorbike | beach trip | | id | class | freq | |---:|:------|:-----| | 1 | car | 0.5 | | 2 | car | 1 | | 3 | motorbike | 1.5 | | 4 | motorbike | 2 | So, a vehicle can be of class "car" or "motorbike", and based on that, can be assigned 1 maintenance frequency value in (0.5, 1.0) and can be a assigned a function in ("leisure", "work") if it's a car, while for a motorbike the frequencies are (1.5, 2.0) and just ("beach trip") for what concerns function. For simplicity, let's assume a vehicle can be assigned only one "feature" (i.e. can be related to just one row of each "feature table"). I'd like to know how to enforce the right constraints in order to ensure that:

-- This should work
INSERT INTO vehicle(label, class_id, freq_id, fun_id) VALUES
('Alice', 1, 1, 1);

--- This should not work because freq_id=3 is for motorbikes
INSERT INTO vehicle(label, class_id, freq_id, fun_id) VALUES
('Bob', 1, 3, 1)

--- This should not work because fun_id=3 is for motorbikes
INSERT INTO vehicle(label, class_id, freq_id, fun_id) VALUES
('Charlie', 1, 1, 3)

--- Third row should not be possible 
SELECT v.label, c.label as class, f.label as function, mf.freq FROM vehicle as v
INNER JOIN class as c ON c.id = v.class_id
INNER JOIN maintenance_frequency as mf ON mf.id = v.freq_id
INNER JOIN function as f ON f.id = v.fun_id;

| label | class | function | freq | |:------|:------|:---------|:-----| | Alice | car | leisure | 0.5 | | Bob | car | leisure | 1.5 | | Charlie | car | beach trip | 0.5 | [fiddle](https://dbfiddle.uk/9RUY1y5L)

I have 2 tables. First table is parent **COLLECTIONS_OF_MAPS** | id | title | | -------- | -------------- | | 50013 | Geological Map Series | | 50014 | Climate Map Series | Second table is a child (but whilst describing map layers, it also has entries that are more like group layers, like parents to child maps within the same table, so there is a recursion element here) - this is then realised in a treeview control in software, just for context purposes. **MAP_LAYERS** | id | parent_layer_id | name | | -------- | -------------- |-------------- | | 1233 | 50013 | Hillshade | | 1228 | 50013 | Cultural Data | | 1231 | 50013 | Rock Types | | 1235 | 1233 | Offshore Hillshade | | 1234 | 1233 | Onshore Hillshade | | 1229 | 1228 | World Cities | | 1230 | 1228 | World Countries | | 1245 | 1228 | World Rivers | | 1270 | 1231 | Sedimentary Rock | | 1271 | 1231 | Igneous Rock | | 1272 | 1231 | Metamorphic Rock | I have just shown an example for the first map collection - "Geological Map Series". As you can see it the MAP_LAYERS parent_layer_id column references entries from BOTH tables; it's parent and itself. Can I create a foreign key rule to that references the ids of itself and or its parent table?

In Postgres, I have an existing table: things | thing_id | thing_name | destination_id | | -------- | ---------- | -------------- | | 10 | thing 10 | null | | 15 | thing 15 | null | For each row in that table, I want to add rows to two new related tables one of which has an auto-generated ID. I then want to update rows in the old table to point to related new rows. Desired result: things | thing_id | thing_name | destination_id | | -------- | ---------- | -------------- | | 10 | thing 10 | 1 | | 15 | thing 15 | 2 | parent_destination | destination_id | | -------------- | | 1 | | 2 | child_destination | destination_id | destination_name | | -------------- | ---------------- | | 1 | [destination] thing 10 | | 2 | [destination] thing 15 | I have tried to do it here: https://www.db-fiddle.com/f/6iyvCT7BYXPHPi2N2HvNor/1 but I can't work out how to return the correct data from result1.