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
378
votes
5 answers

Create unique constraint with null columns

I have a table with this layout: CREATE TABLE Favorites ( FavoriteId uuid NOT NULL PRIMARY KEY, UserId uuid NOT NULL, RecipeId uuid NOT NULL, MenuId uuid ); I want to create a unique constraint similar to this: ALTER TABLE Favorites ADD…
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
279
votes
38 answers

What's wrong with foreign keys?

I remember hearing Joel Spolsky mention in podcast 014 that he'd barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent data integrity problems throughout your database. Do…
ljs
  • 37,275
  • 36
  • 106
  • 124
64
votes
1 answer

MySQL foreign key to allow NULL?

I'm piecing together an image website. The basic schema's pretty simple MySQL, but I'm having some trouble trying to represent possible admin flags associated with an image ("inappropriate", "copyrighted", etc.). My current notion is as…
kyle
  • 1,460
  • 2
  • 15
  • 21
63
votes
3 answers

In MySQL, can I defer referential integrity checks until commit

As in this question, I've been reading PoEAA and wondering if it's possible to defer referential integrity checks until commit in MySQL. I've run into this problem when wanting to insert a bunch of products and related products in the same commit.…
Ross McFarlane
  • 4,054
  • 4
  • 36
  • 52
50
votes
4 answers

CONSTRAINT to check values from a remotely related table (via join etc.)

I would like to add a constraint that will check values from related table. I have 3 tables: CREATE TABLE somethink_usr_rel ( user_id BIGINT NOT NULL, stomethink_id BIGINT NOT NULL ); CREATE TABLE usr ( id BIGINT NOT NULL, role_id…
lukaszrys
  • 1,656
  • 4
  • 19
  • 33
42
votes
3 answers

Rails: delete cascade vs dependent destroy

Assuming I have two tables: users and orders. A user has many orders, so naturally there is a foreign key user_id in my orders table. What is the best practice in rails (in terms of speed, style and referential integrity) to ensure that if a user is…
apotry
  • 1,856
  • 2
  • 17
  • 23
36
votes
9 answers

Maintaining Referential Integrity - Good or Bad?

We are planning on introducing simple Audit Trail in our database using triggers and separate history table for each table that requires auditing. For example consider table StudentScore, it has few foreign keys (eg. StudentID, CourseID) linking it…
YetAnotherUser
  • 9,156
  • 3
  • 39
  • 53
36
votes
7 answers

How do I disable referential integrity in Postgres 8.2?

Google results on this one are a bit thin, but suggest that it is not easily possible. My specific problem is that I need to renumber the IDs in two tables that are related to each other such that table B has an "table_a_id" column in it. I can't…
sanbikinoraion
  • 752
  • 2
  • 8
  • 12
35
votes
5 answers

Why do Rails migrations define foreign keys in the application but not in the database?

If I define a Customer and Order model in which a Customer "has many" Orders and the Order "belongs to" the Customer, in Rails we talk about Order having a foreign key to the Customer through customer_id but we don't mean that this is enforced in…
eggdrop
  • 3,356
  • 5
  • 29
  • 32
23
votes
11 answers

Should referential integrity be enforced?

One of the reasons why referential integrity should not be enforced is performance. Because Db has to validate all updates against relationships, it just makes things slower but what are the other pros and cons of enforcing and not…
Muhammad Hasan Khan
  • 34,648
  • 16
  • 88
  • 131
21
votes
2 answers

SET CONSTRAINTS ALL DEFERRED not working as expected

In a PostgreSQL 9.3 database, if I define tables a and b as follows: CREATE TABLE a(i integer); ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i); CREATE TABLE b(j integer); ALTER TABLE b add CONSTRAINT fkey_ij FOREIGN KEY (j) REFERENCES a…
user79074
  • 4,937
  • 5
  • 29
  • 57
20
votes
6 answers

mysql circular dependency in foreign key constraints

Given the schema: What I need is having every user_identities.belongs_to reference an users.id. At the same time, every users has a primary_identity as shown in the picture. However when I try to add this reference with ON DELETE NO ACTION ON…
Flavius
  • 13,566
  • 13
  • 80
  • 126
19
votes
5 answers

NoSQL / RDBMS hybrid with referential integrity (delete cascade)?

Is there a database out there that gives you the benefit of referential integrity and being able to use a SQL type language for querying, but also lets entities be loosely defined with respect to their data attributes and also the relationships…
SeriousCallersOnly
  • 429
  • 3
  • 6
18
votes
4 answers

SQL Server: how to know if any row is referencing the row to delete

You cannot delete a row if any row is referencing the row to delete via a FK. Is it possible to know if any row is referencing the row to delete before executing a DELETE statement?
Yeonho
  • 3,629
  • 4
  • 39
  • 61
17
votes
6 answers

How can I break referential integrity briefly, within a transaction, without disabling the foreign key constraint?

I have a table with 3 columns: ID, PARENT_ID, NAME PARENT_ID has a foreign key relationship with ID in the same table. This table is modeling a hierarchy. Sometimes the ID of a record will change. I want to be able to update a record's ID, then…
aw crud
  • 8,791
  • 19
  • 71
  • 115
1
2 3
…
23 24