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
5
votes
1 answer

Database triggers / referential integrity and in-memory caching

Do you see database triggers / referential integrity rules being used in a way that changes actual data in the database (changing row w in table x causes a change in row y in table z)? If yes, How does this tie-in with the increasing popularity of…
Ran Biron
  • 6,317
  • 5
  • 37
  • 67
5
votes
1 answer

Alternatives to FK SET NULL design that causes cycles

Possible Duplicate: Foreign key constraint may cause cycles or multiple cascade paths? I have a database of contact details that contains four primary tables: Organisations Departments Sections People Here is a diagram (the arrows…
Asad Saeeduddin
  • 46,193
  • 6
  • 90
  • 139
5
votes
4 answers

Is it possible to defer referential integrity checks until the end of a transaction in SQL Server?

I recently read in Fowler's PofEA that some database engines allow you to defer RI checks until the end of the transaction. Is this possible in SQL Server? The problem comes with some scenarios where I am trying to save changes that include…
Darrel Miller
  • 139,164
  • 32
  • 194
  • 243
5
votes
4 answers

Enforce Referential Integrity on Materialized Path?

I'm trying to implement a tree like structure using a Materialized Path model described here: http://www.dbazine.com/oracle/or-articles/tropashko4. Is it possible to enforce referential integrity on the [path] field? I don't see how SQL could do…
hyperslug
  • 3,473
  • 1
  • 28
  • 29
5
votes
1 answer

ElevateDB relational model do’s and dont’s

My company uses ElevateDB in one of our products. However, our database is just a bunch of tables. Recently, as more features are designed and more modules are added, we have found out our data is no longer “bunch of unrelated tables”, but it begins…
smok1
  • 2,940
  • 26
  • 35
5
votes
6 answers

Syncing referential integrity tables and enums

I ponder this question from time to time, so I thought I'd ask you guys about it. Let's say I have a database table that looks like this: Table: Visibility Id Value -- ----- 0 Visible 1 Invisible 2 Collapsed This is just a table for…
devuxer
  • 41,681
  • 47
  • 180
  • 292
5
votes
4 answers

MYSQL - One Column Referenced to Multiple Table

Can a single column in a table can be referenced to multiple tables?
rechie
  • 2,139
  • 5
  • 25
  • 38
4
votes
2 answers

On delete restrict mysql is not working

I want to prevent deleting from parent table when he has children in other tables that. I make like this ALTER TABLE constant_det_tb ADD CONSTRAINT fk_idparent FOREIGN KEY (idparent) REFERENCES constant_tb(id) ON DELETE RESTRICT When I delete from…
palAlaa
  • 9,500
  • 33
  • 107
  • 166
4
votes
1 answer

postgresql and Delete statement violates foreign key constraint

I have a problem with my delete statement. I have two tables: table vehicule_loan( vehicule TEXT NOT NULL UNIQUE, ); table vehicule_uid ( id UUID NOT NULL DEFAULT uuid_generate_v4(), vehicule TEXT NOT NULL REFERENCES…
Pompom Pidou
  • 135
  • 1
  • 3
  • 7
4
votes
1 answer

SQL Server - Maintain Referential Integrity without CASCADE and INSTEAD OF trigger

I have a table (TableB) that has a foreign key relationship with a parent table (TableA). When I delete a record in Table A, I want to preserve referential integrity by deleting all records in TableB that reference the deleted record in…
Brett Postin
  • 11,215
  • 10
  • 60
  • 95
4
votes
3 answers

Are there any benefits to using MySQL for anything other than as a 'dumb' data-store?

The reason I ask is that we'd like to use a certain CHECK constraint which MySQL currently doesn't suport. Without this type of constraint in place, the whole reason for using foreign keys and referential integrity seems to diminish as the…
Bendos
  • 255
  • 2
  • 11
4
votes
2 answers

Use triggers on inherited tables to replace foreign keys

I'm new to PostgreSQL. I have tables like: CREATE TABLE Person ( ID SERIAL PRIMARY KEY, Name VARCHAR(32) NOT NULL DEFAULT '', Surname VARCHAR(32) NOT NULL DEFAULT '', Birthday DATE, Gender VARCHAR(8) ); -- Student table inherits from…
pikk
  • 525
  • 1
  • 12
  • 26
4
votes
2 answers

How can I check for referential integrity in a given row after the row has been added?

I'm trying to see if there is a way to check the referential integrity of a single row after it has been inserted into an InnoDB table while foreign_key_checks is set to 0. So given two tables: CREATE TABLE `book` ( `id` int(11) NOT NULL…
jsdalton
  • 6,555
  • 4
  • 40
  • 39
4
votes
3 answers

Three-way Referential Integrity - SQL Server 2008

I am building a database using SQL Server 2008 to store prices of securities that are traded on multiple markets. For a given market, all the securities have the same holiday calendar. However, the holiday calendars are different from market to…
OracleOfNJ
  • 75
  • 1
  • 5
4
votes
2 answers

There is a way to enforce referential integrity in MongoDB

When you search for referential integrity in relation to Mongo-DB the standard response is "MongoDB does not support this". The standard explanation is that MongoDB supports refs and populate, however there is nothing that prevents you changing the…
Mike Thomson
  • 141
  • 2
  • 8
1 2 3
…
23 24