Stack Overflow
Stack Overflow

Questions tagged [referential-integrity]

Referential integrity is a property of data which requires the value of an attribute/column of a relation table to exist as a value of another attribute/column in another relation table

For referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain either a null value, or only values from a parent table's primary key or a candidate key. In other words, when a foreign key value is used it must reference a valid, existing primary key in the parent table.

http://en.wikipedia.org/wiki/Referential_integrity

353 questions
1
vote
1 answer

Many-to-Many Link Table Foreign Key Modeling in SQLite

I have the following two tables in SQLite: CREATE TABLE `Link` ( `link_id` integer NOT NULL, `part_id` integer NOT NULL, CONSTRAINT `link_pk` PRIMARY KEY(`link_id`,`part_id`) ); CREATE TABLE `Main` ( `main_id` integer NOT NULL…
byteit101
  • 3,910
  • 2
  • 20
  • 29
1
vote
1 answer

Forbidding insertion of integer not in rowid in SQLite (keep directed acyclic graph table consistent)

I'd like to express: "insertion of record with 'parent' value that is not included in 'rowid' AFTER INSERTION is forbidden." My intention is to keep the table internally consistent as a directed acyclic graph, with every record being a node…
Raoul
  • 1,872
  • 3
  • 26
  • 48
1
vote
1 answer

How to prevent referential integrity constraint violation in tests?

I use JUnit 5 to create unit tests for my Spring Boot application. For each testing class, I use @Sql to load the context: HierarchyEntityServiceTest.java @SpringBootTest @ActiveProfiles("test") @Sql({ "/sql/import_hierarchy_entity_type.sql", …
Nerah
  • 66
  • 6
1
vote
1 answer

Creating a trigger not allowing a value that isn't in another column

I have a question about a trigger that I would like to create and I am not sure how to do this. I have a table, 2017_state_data, and it contains some financial metrics per state along with said state name. Now it's important that the state name is…
user12973342
1
vote
1 answer

Reference to one OR another table in Oracle SQL

I have one table OBRAZAC Columns are: JIB_OBRASCA JIB_NARUDZBENICE TIP_NARUDZBE IME_ZAPOSLENOG PREZIME_ZAPOSLENOG JIB_KLINIKE NAZIV_ODJELJENJA Depending on the value in column TIP_NARUDZBE which can be "M" or "L", JIB_OBRASCA should reference to…
mIl3
  • 13
  • 3
1
vote
0 answers

Complex Ref. Integrity in Oracle: child with multiple parents, non-key joins

My predecessor built our database with some "overloaded" child tables that are shared by multiple parents, using a "tabletype" column that specifies which parent table is the parent of a particular child record. Also, the parents and child are…
mempie
  • 11
  • 2
1
vote
1 answer

Is there any way to force SQLite constrains checks?

For example, let say DB has foreign key A.b_id -> B.id with SET NULL on delete. If record with some B.id get deleted, all b_id references will be set to NULL. But if A already contains record where A.b_id has value that is not in B.id (it was…
Oleg
  • 101
  • 7
1
vote
2 answers

Ensure there's exactly one foreign key reference from a set of tables

I'm trying to design a database structure that allows me to extract common fields into one table called "entity". You could think of "entity" as an abstract class. Every "entity" has an owner (a referenced user-account), a creation-time and some…
Felix
  • 2,256
  • 2
  • 15
  • 35
1
vote
1 answer

mySQL repair new database integrity implementing foreign keys

My problem is fairly simple. I was been given around 95% of a fairly large, working data set. And I wrote out a parser, set my data out into CSVs, wrote my bulk loads, and have everything set up save the foreign keys. Now, part of the lost 5% is…
robinspi
  • 93
  • 7
1
vote
6 answers

Broken referential integrity: What would Edgar Codd say?

I'm trying to understand rules of relational model as originally defined by Edgar Codd in 1970. Specifically I'm interested whether referential integrity is part of his relational model or not. I'll try to demonstrate on following example (just to…
lubos hasko
  • 24,752
  • 10
  • 56
  • 61
1
vote
1 answer

Which type of iterable/list in Python is able to structurally share the same items from previous iterables/lists?

Say I have two long lists a and b and want a list c: a = [obj1, obj2, ..., objN] b = [objNplus1, objNplus2, ..., objNplusM] c = a + b How can I create a new list that shares the previous ones as its start and end, but without violating the…
dawid
  • 663
  • 6
  • 12
1
vote
4 answers

How to enforce a one-many relationship between a column in one table and a column in two parent tables?

I have three tables in an existing database application. I don't have ERDs or other documentation about the table relations. There are no existing primary or foreign keys. I would like to model the relation and ideally enforce it with…
jon
  • 31
  • 2
1
vote
1 answer

What is the way proper way to manage Rails ActiveRecord associations?

I am new to Rails so bear with me… I am trying to create a set of data related to 2 different models. I currently have the following models: class M < ActiveRecord::Base belongs_to :u belongs_to :s end class U < ActiveRecord::Base has_many :m …
machunter
  • 967
  • 1
  • 11
  • 27
1
vote
2 answers

Referential Integrity violation

I´m getting the next error message: System.Data.Entity.Infrastructure.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.' Basically I have two classes, Type and Device. public class Device { …
Raúl Eduarte
  • 21
  • 2
1
vote
3 answers

MySQL relationships & constraints, should I use them?

I've built a large database in MySQL and created all the relationships between the tables with SQLYog. That's all well and good but in my PHP site that I'm building I often have trouble when it comes to deleting rows from the database, I often run…
zuallauz
  • 4,328
  • 11
  • 43
  • 54
1 2 3
…
23 24