Skip to main content

Questions tagged [referential-integrity]

Facilities provided by a database management system to ensure consistency within the data.

23 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7 votes
1 answer
333 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 ...
pvgoran's user avatar
  • 171
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 ...
wchargin's user avatar
  • 171
3 votes
0 answers
518 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 ...
user3607758's user avatar
3 votes
0 answers
155 views

Visualize the effects of a cascading delete or update?

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? ...
Jacco's user avatar
  • 131
2 votes
0 answers
49 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 ...
Him's user avatar
  • 133
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 ...
rdbisme's user avatar
  • 121
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 ...
CuriousGeorge's user avatar
2 votes
0 answers
5k views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC sp_msforeachtable ...
Krzysztof Antoniak's user avatar
2 votes
0 answers
76 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. Activities ...
Willem-Aart's user avatar
2 votes
0 answers
59 views

SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes

Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" ...
G21's user avatar
  • 121
2 votes
0 answers
342 views

Maintaining Referential Integrity with a Filestream enabled database

We have a production database which is filestream enabled and has grown, within a year, to about 20Gb as it stores documents. On many occasions we need to bring the live data back to UAT or Dev ...
John J Smith's user avatar
1 vote
1 answer
59 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: ...
lucifer34's user avatar
1 vote
1 answer
931 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'...
untitled90's user avatar
1 vote
0 answers
342 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 ...
Xudla's user avatar
  • 11
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 ...
user180147's user avatar
1 vote
0 answers
48 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) ...
spc2012's user avatar
  • 11
1 vote
0 answers
786 views

Constraint across multiple tables

I'm modeling a system where with vendors, each of which sells some overlapping subset of products available in the universe. Products (T-shirt) come in various variants (Small, Medium, Large) with ...
spike's user avatar
  • 293
1 vote
0 answers
191 views

Enforce referential integrity for FriendRequest table that has UserA and UserB

I have a table called User that has UserName as a primary key. I want to establish a relationship with enforced referential integrity between User and FriendRequest. FriendRequest has the following: ...
poetryrocksalot's user avatar
0 votes
1 answer
39 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. ...
LNicole's user avatar
0 votes
0 answers
54 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 ...
Joe DiNottra's user avatar
0 votes
0 answers
191 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 ...
nowox's user avatar
  • 123
0 votes
2 answers
56 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 ...
zv426's user avatar
  • 29
0 votes
0 answers
27 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 ...
slondike's user avatar