Questions tagged [referential-integrity]
Facilities provided by a database management system to ensure consistency within the data.
128 questions
2 votes
1 answer
410 views
What is an industry standard name for this self-referencing foreign key relationship?
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 ...
- 153
2 votes
1 answer
253 views
Create Constraint To Ensure Date Range Is Within Another Date Range
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: ...
- 21
1 vote
1 answer
2k views
Modeling Polymorphic Relations in Postgres 15 with bi-directional (cyclic) FK constraints
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'...
- 11
1 vote
2 answers
87 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 | ...
- 147
0 votes
1 answer
59 views
ER Diagram for Missed Visits
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. ...
- 1
2 votes
0 answers
61 views
Modeling strictly referential relationships to third-party databases
To be clear, I mean to ask this question from a strictly "Data Modelling and database-design, including referential-integrity" PoV. I am currently designing a database that will serve as an ...
- 133
1 vote
0 answers
467 views
Use 2 columns of a table as foreign key in an other table to avoid circular-reference
I have two tables: ObjectContainer containerID INT NOT NULL PRIMARY KEY mainObjectID INT NOT NULL FOREIGN KEY REFERENCES Object (objectID) Object objectID INT NOT ...
- 11
2 votes
1 answer
82 views
Referencial integrity for hierarchical tables
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 ...
- 121
3 votes
2 answers
2k views
For each row create a linked row in another table with auto-generated ID
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 ...
- 187
0 votes
1 answer
366 views
How can I change the REFERENCE in a FOREIGN KEY?
After renaming some tables I get the following error: MariaDB [testdb]> INSERT INTO user_events (date, uid, operation, info) VALUES('2022-09-15','xyz','create',NULL); ERROR 1452 (23000): Cannot add ...
- 103
1 vote
1 answer
1k views
Foreign Key - from same table and parent table
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 ...
- 157
0 votes
0 answers
71 views
Are there performance issues with FKs on different schemas?
In order to perform functional decomposition of a "big blob" of a database the first step we wanted to take is to separate different modules of the database into separate schemas on the same ...
- 101
7 votes
2 answers
2k views
Foreign key to multiple tables via forwarding table [duplicate]
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 ...
- 171
0 votes
2 answers
280 views
Oracle parent child move failed
I have two tables, a parent and child table, which have referential integrity declared between two tables and try to move both the tables so I can delete rows. I start with the child table and the ...
- 105
0 votes
3 answers
589 views
How can I define circular referential integrity constraints?
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 ...
