Questions tagged [referential-integrity]
Facilities provided by a database management system to ensure consistency within the data.
128 questions
2
votes
1
answer
166
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
1
vote
1
answer
150
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: ...
- 11
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
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
0
votes
1
answer
42
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
54
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
437
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
61
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 ...
- 187
0
votes
1
answer
320
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 ...
- 157
0
votes
0
answers
58
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
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
242
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
502
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
70
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
96
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
592
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
41
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
1k
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
193
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
1k
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
63
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
39
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
4
votes
2
answers
444
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,086
0
votes
1
answer
316
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
364
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
38
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
47
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
1k
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
107
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
26
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
29
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
119
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
52
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, ...
- 155
2
votes
2
answers
130
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
92
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
160
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,025
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
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
536
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
253
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
31
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
216
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'...
- 70.5k
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
...
- 70.5k
2
votes
4
answers
713
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