Questions tagged [referential-integrity]

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

16 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
195 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 ...
2
votes
0answers
49 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 ...
2
votes
1answer
84 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 ...
2
votes
0answers
105 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? ...
2
votes
0answers
68 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 ...
2
votes
0answers
54 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" ...
2
votes
0answers
295 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 ...
1
vote
1answer
97 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 ...
1
vote
0answers
22 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 ...
1
vote
0answers
26 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) ...
1
vote
0answers
4k 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 ...
1
vote
0answers
534 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 ...
1
vote
0answers
120 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: ...
0
votes
0answers
47 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 ...
0
votes
1answer
22 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 ...
0
votes
0answers
14 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 ...