Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [referential-integrity]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
34 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 ...
user avatar
2 votes
0 answers
40 views

Foreign key to multiple tables via forwarding table

Problem statement I have a table with a column whose values are foriegn keys, but the target table of the foreign key differs from row to row. The relevant table can be determined from the key value ...
user avatar
  • 121
0 votes
2 answers
36 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 ...
user avatar
  • 103
0 votes
3 answers
83 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 ...
user avatar
0 votes
2 answers
29 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 ...
user avatar
0 votes
2 answers
32 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 ...
user avatar
4 votes
3 answers
251 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 ...
user avatar
  • 161
0 votes
1 answer
34 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) ...
user avatar
  • 105
4 votes
3 answers
256 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 ...
user avatar
  • 143
0 votes
0 answers
89 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 ...
user avatar
  • 123
-1 votes
4 answers
159 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 ...
user avatar
0 votes
2 answers
38 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 ...
user avatar
  • 29
0 votes
1 answer
25 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 ...
user avatar
  • 103
3 votes
2 answers
185 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 ...
user avatar
0 votes
1 answer
98 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 ...
user avatar
5 votes
1 answer
173 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 ...
user avatar
  • 151
-1 votes
2 answers
210 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, `...
user avatar
1 vote
1 answer
33 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 ( ...
user avatar
  • 113
1 vote
1 answer
38 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 ...
user avatar
4 votes
1 answer
1k 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 ...
user avatar
2 votes
1 answer
246 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 ...
user avatar
2 votes
1 answer
77 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, ...
user avatar
1 vote
0 answers
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 ...
user avatar
0 votes
0 answers
22 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 ...
user avatar
1 vote
1 answer
868 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 ...
user avatar
0 votes
2 answers
57 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 ...
user avatar
  • 101
1 vote
0 answers
31 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) ...
user avatar
  • 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, ...
user avatar
  • 145
2 votes
2 answers
85 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 ...
user avatar
5 votes
3 answers
81 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 ...
user avatar
0 votes
1 answer
143 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 ...
user avatar
  • 1,005
25 votes
2 answers
23k 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 ...
user avatar
2 votes
2 answers
931 views

Conditional table reference

I have a table tree: +------+-------+ | id | name | +------+-------+ | 0 | tree1 | | 1 | tree2 | | 2 | tree3 | | 3 | tree4 | +------+-------+ A table pen: +------+------+ | id | ...
user avatar
  • 123
3 votes
0 answers
377 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 ...
user avatar
3 votes
2 answers
157 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 ...
user avatar
  • 31
1 vote
1 answer
26 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 ...
user avatar
  • 111
1 vote
3 answers
201 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'...
user avatar
  • 67.1k
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 ...
user avatar
  • 67.1k
2 votes
4 answers
478 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) | ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
1 vote
1 answer
94 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 ...
user avatar
  • 1,199
8 votes
2 answers
1k views

Is having relationships between separate databases bad practice?

I am working with a client that has multiple databases. There are several master level databases that have relationships back to them from instance level databases (application specific DBs). The ...
user avatar
  • 183
6 votes
2 answers
8k 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 ...
user avatar
  • 2,178
1 vote
2 answers
2k views

Deleting would-be orphans when creating FK in Postgres

I am working on a script that replicates schema and data. I first create all the structure except for foreign keys after reading it from the source's system tables, then copy the data, then create the ...
user avatar
0 votes
1 answer
383 views

Check integrity between servers

I have three Database servers, running PostgreSQL 9.2: master01 (Own data center USA) slave01 (Own data center USA) slave02 (AWS Australia) Both slaves use streaming replication + ...
user avatar
11 votes
4 answers
67k 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 ...
user avatar
  • 111
0 votes
1 answer
187 views

Simple way to list order of referential integrity in Postgres

Is there a clean, simple, and preferably prepackaged (ready function?) way to list the order of referential integrity in a Postgres database, rather than trying to read the system tables to find that ...
user avatar
1 vote
1 answer
596 views

How do triggers and check constraints relate to maitaining database rules

I was looking at a tutorial on triggers for SQLite. The tutorial said that a use of triggers could be for data validation. The following code was given as an example: CREATE TRIGGER ...
user avatar
1 vote
1 answer
1k views

Make a boolean column unique per group

I want to ensure consistency and make sure only one phone line is the default for a house. Schema: PhoneLine ID | isDefault | HouseID I want to add a trigger so that when you add/update a row, and ...
user avatar
  • 113
2 votes
2 answers
766 views

DB Design. Tracking history without losing referential integrity

To make it simple, let’s say we have these 2 tables: CREATE TABLE `tbl_companies` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `tbl_workers` ( `...
user avatar
  • 23