Questions tagged [referential-integrity]
Facilities provided by a database management system to ensure consistency within the data.
126
questions
1
vote
1
answer
272
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
43
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
0
votes
1
answer
33
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
37
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 ...
- 121
1
vote
0
answers
238
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
54
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
993
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 ...
- 177
0
votes
1
answer
214
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
957
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 ...
- 157
0
votes
0
answers
50
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
1k
views
Foreign key to multiple tables via forwarding table
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
144
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
380
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 ...
0
votes
2
answers
46
views
Designing complex cross table data integrity checks
I am trying to start working on an application, and while I have some code the time has come to work on my database model as well. In the next sections I will describe what my application should be ...
- 101
0
votes
2
answers
84
views
Want to Set Relationship Between 3 Tables (1st Child Table column value depends between two another table columns)
I am using Visual Basic with a Microsoft SQL Server Database for my WinForms Application.
I have 3 Tables - Purchases, Sales, Transactions (for payment/receipt)
In the Transactions table, I am using ...
4
votes
3
answers
514
views
Inconsistency in Relational Databases
I generally get confused when the term consistency is used. NoSQL tutorials always refer to the reads whereas Relational tutorials refer to a consistent state (ensuring referential integrity ...
- 161
0
votes
1
answer
38
views
SQL Server - ensuring a complex relation on insert
I have some related tables:
Application - stores a list of applications
ApplicationInstall - for each application, multiple installations are present (I have a foreign key ApplicationUid)
...
- 105
4
votes
3
answers
858
views
Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?
I have a requirement for a very large (in terms of the number of rows, not data) database, and need to use a very long, unique, hexadecimal PRIMARY KEY instead of the usual AUTO_INCREMENT integer.
As ...
- 143
0
votes
0
answers
168
views
Ensure data integrity in relational DB?
Let's imagine I want to store in a relational database the following :
A list of quizzes
A list of questions associated with quizzes
A list of users
The answers to questions made from users.
I could ...
- 123
0
votes
4
answers
865
views
How to store a (record which holds a) reference to any other column (attribute) in another table (relation)
TL;DR: If the database schema should hold all the business logic, how is it be possible to specify that an attribute type is a reference to a specific attribute, instead of a specific record (as is ...
0
votes
2
answers
48
views
Foreign Key Settings
I will start by mentioning that I am very new to databases. I am working in MySQL.
Can anyone give me a layman's view of when you would use which settings for the foreign key options so when you would ...
- 29
0
votes
1
answer
31
views
Ensure integrity of other columns from foreign record
I have 3 tables:
Race
- id PK
Racer
- id PK
- rank
Winner
- id PK
- race_id FK(Race.id)
- racer_id FK(Racer.id)
- rank
- prize
UNIQUE CONSTRAINT (race_id, rank)
Racer.rank is a non-unique ...
- 103
3
votes
2
answers
345
views
Referential Integrity - Indirect Foreign Key In "Depth"
Question
What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship ...
- 1,031
0
votes
1
answer
278
views
Better way to ensure referential integrity of one column to a composite primary key?
Given a first table
CREATE TABLE table1 (
column1 INTEGER,
column2 TEXT,
PRIMARY KEY (column1, column2));
does SQL have an idiomatic way to constrain column3 in a second table
CREATE ...
- 103
7
votes
1
answer
297
views
Do any of the graph based/aware databases have good mechanisms for maintaining referential integrity?
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 ...
- 171
-1
votes
2
answers
1k
views
Foreign key twice in same table for different columns?
Server: MariaDB 10.3.21
Client: MariaDB 10.4.12
Given the following structure/data:
DROP TABLE IF EXISTS `main`;
CREATE TABLE `main` (
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`...
- 101
1
vote
1
answer
35
views
two-table-over <+> functional relationship: maintaining referential integrity
I have three tables, the last two having a many to one relationship with the first, written here in SQL:
create table study (
id bigint primary key,
name text
);
create table treatment (
...
- 113
1
vote
1
answer
42
views
Data Deplucation and relationship integrity [closed]
I apologize in advance as this question is likely to be more on the theoretical side, with (hopefully) a fair bit of discussion.
This is the scenario: We have Salesforce and a multiple local ...
- 193
4
votes
1
answer
2k
views
Complex Delete Cascade Including Self-Referencing Table
Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I ...
- 143
2
votes
2
answers
791
views
How to delete foreign key with lots of references
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 ...
2
votes
1
answer
101
views
Hierarchical references abstraction with strict model
I want to abstract a real world hierarchy in a strict DB model (as strict as possible).
My problem is to to create a structure with a this-or-this-but-not-both rules set.
I have two entity types,
...
- 734
1
vote
0
answers
25
views
Referential integrity based on data more than a table away
Notes on tables A, B, C, D, E
B.aid and C.aid reference A.aid.
D.cid references C.cid and D.did is the primary key. D is a C.
cid and did are pre-assigned and I cannot change them (they need to ...
- 11
0
votes
0
answers
26
views
More than one path between M-M tables
I'm trying to figure out if it is bad practice to have more than one path between tables that are related via many-to-many relationships.
I have 3 user-facing tables: Dupe, Cue, and Take. There is a ...
- 1
1
vote
1
answer
1k
views
Foreign key constraints check during replication?
I am a little bit new to replication and can't seem to find an answer to this anywhere.
Let's say I have a simple relation in my database: Department table with Code column as PK and Person table ...
- 263
0
votes
2
answers
87
views
How to model very flexible referencing relationships between many tables?
I have various different entity types in my database. For purposes of example, consider an IMDB database:
Movie
Person
Actor
Director
Translation
and 20 more tables
My application enables free-form ...
- 101
1
vote
0
answers
40
views
Redundant column to maintain data integrity
Consider the following example:
Syllabus
-id
-course_id
FOREIGN KEY (course_id) REFERENCES Course(id)
SyllabusItem
-id
-syllabus_id
FOREIGN KEY (syllabus_id) REFERENCES Syllabus(id)
...
- 11
-1
votes
1
answer
34
views
Keeping reference in an array format
I have a design with an orders table and products table. Each order can have many products, so 1 order N products.
The default design would be to have something like this:
orders table
order_id, ...
- 145
2
votes
2
answers
117
views
Enforcing existence of records while avoiding circular references
I'm having issues designing a satisfactory solution for the following problem.
There are two main entities:
Customer
User
A customer can have many users associated to it. A user can be associated ...
- 173
5
votes
3
answers
85
views
Constraining data based on related data in a different table. Should this be done on the database level or the application level?
I am working on a database that contains our customer information as well as information about our vendors. Part of that includes matching our customers with our vendors, the account numbers those ...
- 185
0
votes
1
answer
152
views
FK to table with non-unique values via a on commit refreshing materialized view?
I would like to create a FK to a table without unique values. So I cannot create a simple FK. [to clarify, the parent table has duplicates - dont ask why, not in my control ]
I tried creating a ...
- 1,005
35
votes
2
answers
37k
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 ...
- 451
2
votes
2
answers
1k
views
Conditional table reference
I have a table tree:
+------+-------+
| id | name |
+------+-------+
| 0 | tree1 |
| 1 | tree2 |
| 2 | tree3 |
| 3 | tree4 |
+------+-------+
A table pen:
+------+------+
| id | ...
- 123
3
votes
0
answers
509
views
Orphan records after delete statement in stored procedure
I have a database where some of the tables have parent --> child --> grandchild relationships through foreign keys as shown below.
CREATE TABLE Parent
(
ParentID INT UNSIGNED PRIMARY KEY ...
- 61
3
votes
2
answers
223
views
Create foreign key constraint on indirect data
I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database:
CREATE TABLE logger (
id INTEGER PRIMARY KEY
);
CREATE TABLE ...
- 31
1
vote
1
answer
30
views
Modelling a second level table relationship
I have three tables in my Database:
Customers
----------
Id
Name
Addresses
----------
Id
Description
CustomerId
Orders
----------
Id
Description
CustomerId
DeliveryAddressId
I'd like to make ...
- 111
1
vote
3
answers
209
views
Determinism inside an INSTEAD OF trigger
In reference to my prior question about a preferred design to avoid circular or multiple update path referential integrity cascading updates and deletes via ON UPDATE CASCADE and ON DELETE CASCADE, I'...
- 69.7k
5
votes
2
answers
2k
views
Preferred design to avoid circular/multiple update paths
Consider the following design:
CREATE TABLE dbo.Farmers
(
FarmerName varchar(10) NOT NULL
PRIMARY KEY
);
CREATE TABLE dbo.FarmEquipment
(
FarmEquipmentName varchar(10) NOT NULL
...
- 69.7k
2
votes
4
answers
644
views
Referential integrity when two FK columns need to match themselves in the same table?
It's possible my design is wrong, or there's simply a better way. I'll use a very simple example:
--------- dbo.Book----------
| |
| BookID int identity (1,1) |
...
- 203
1
vote
1
answer
3k
views
How do I set up primary and foreign keys referencing two different tables?
I need to have two primary keys also to be the foreign keys in the same table. These two keys are referenced in different tables. I keep getting error messages that are unclear to me. My code is as ...
1
vote
1
answer
101
views
Is there another source for untrusted foreign keys beside FK disable and BULK inserts?
This question and its answer explain how bulk inserts will render FK as untrusted, as bulk inserts are not completely checked (only PK and UNIQUEs) . I also know that temporarily disable of a FK may ...
- 1,261