Skip to main content

Questions tagged [referential-integrity]

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

Filter by
Sorted by
Tagged with
37 votes
2 answers
41k 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 ...
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 ...
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 ...
1 vote
1 answer
946 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'...
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 ...
1 vote
1 answer
61 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: ...
59 votes
2 answers
58k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
1 vote
2 answers
53 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 | ...
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? ...
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. ...
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 ...
13 votes
3 answers
13k views

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it. quote from MySQL 5.7 Reference Manual / GRANT Syntax REFERENCES ...
1 vote
0 answers
349 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 ...
14 votes
2 answers
9k views

Do I need a separate Id column for this "mapping" table?

I have a table of Producers and a table of Products, both of which are of the form: Id - int, Primary key Name - nvarchar A Producer can carry multiple Products, so I was going to create a table ...
8 votes
2 answers
6k views

"Merge" two rows in a Postgres table, with foreign keys

I am keeping a database of books I've read, using the following two tables in PostgreSQL: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL ...
0 votes
3 answers
445 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 ...
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 ...
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 ...
3 votes
2 answers
1k 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 ...
0 votes
1 answer
286 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 ...
3 votes
1 answer
1k views

What is the correct way to map relationships between structurally similar comments tables and commentable entities?

I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments. ...
15 votes
1 answer
8k views

Enforcing constraints "two tables away"

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short for ...
33 votes
16 answers
27k views

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
31 votes
3 answers
15k views

Constraint to enforce "at least one" or "exactly one" in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address | ...
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 ...
0 votes
2 answers
210 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 ...
2 votes
1 answer
11k views

ON DELETE CASCADE not working

Trying to implement a simple user follower system in MySQL, using a junction table: CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, username varchar(25) ...
5 votes
1 answer
532 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
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 ...
0 votes
2 answers
58 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 ...
0 votes
2 answers
88 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
578 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 ...
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) ...
4 votes
3 answers
989 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 ...
3 votes
2 answers
235 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 ...
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 ...
6 votes
3 answers
6k views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...
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
1 answer
726 views

SQL - referential integrity - Foreign key and Check constraint

Please let me know if below two declarations are same. create table R1 ( a INT PRIMARY KEY, b INT references K(w)); Note: K is a table with single attribute w as primary key create table R1 ( a ...
0 votes
1 answer
32 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 ...
3 votes
2 answers
395 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 vote
1 answer
103 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 ...
0 votes
1 answer
291 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 ...
-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, `...
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 ...
11 votes
4 answers
78k views

DELETE statement conflicted with the REFERENCE constraint

My situation looks like this: Table STOCK_ARTICLES: ID *[PK]* OTHER_DB_ID ITEM_NAME Table LOCATION: ID *[PK]* LOCATION_NAME Table WORK_PLACE: ID *[PK]* WORKPLACE_NAME Table INVENTORY_ITEMS: ID ...
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 ( ...
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 ...
2 votes
1 answer
103 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, ...
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 ...