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
17
votes
2 answers

"polymorphism" for FOREIGN KEY constraints

There is this field in a table: room_id INT NOT NULL CONSTRAINT room_id_ref_room REFERENCES room I have three 2 tables for two kinds of rooms: standard_room and family_room How to do something like this: room_id INT NOT NULL CONSTRAINT…
Jahongir Rahmonov
  • 13,083
  • 10
  • 47
  • 91
16
votes
2 answers

What are the alternative ways to model M:M relations in Cassandra?

Consider a M:M relation that needs to be represented in a Cassandra data store. What M:M modeling options are available? For each alternative, when is it to prefer? What M:M modeling choices have you made in your Cassandra powered projects?
knorv
  • 49,059
  • 74
  • 210
  • 294
15
votes
5 answers

When is referential integrity not appropriate?

I understand the need to have referential integrity for limiting specific values on entry or possibly preventing them from removal upon a request of deletion. However, I am unclear as to a valid use case which would exclude this mechanism from…
Curtis Inderwiesche
  • 4,940
  • 19
  • 60
  • 82
15
votes
4 answers

(doctrine2 + symfony2) cascading remove : integrity constraint violation 1451

First, sorry for my poor English... I got four entities : User, Application, Bundle & Entity. Here are their relations (with cascading persist & remove, see code below) : User 1-n Application Application 1-n Bundle Bundle 1-n Entity It's working…
bgaze
  • 980
  • 1
  • 8
  • 24
14
votes
4 answers

How to enforce referential integrity on Single Table Inheritance?

I've read some of Bill Karwin's answers about single table inheritance and think this approach would be good for the setup I am considering: Playlist -------- id AUTO_INCREMENT title TeamPlaylist ------------ id REFERENCES Playlist.id teamId…
Matt McCormick
  • 13,041
  • 22
  • 75
  • 83
13
votes
9 answers

Database per application VS One big database for all applications

I'm designing a few applications that will share 2 or 3 database tables and all of the other tables will be independent of each app. The shared databases contain mostly user information, and there might occur the case where other tables need to be…
Jorge Vargas
  • 1,031
  • 11
  • 25
13
votes
3 answers

Does CouchDB supports referential integrity?

I am new to CouchDB and learning about it. I did not come across CouchDB support for referential integrity. Can we create a foreign key for a field in the CouchDB document? For e.g. Is it possible to ensure a vendor name used in a order document is…
Sundar
  • 1,204
  • 1
  • 14
  • 17
13
votes
1 answer

mysql Multiple Foreign Keys in a Table to the Same Primary Key

I have a table user with userID as the primary key. I have another table called Friends. In the Friends table, I have two Users as friends represented by the columns UserID and FrndID where both UserID and FrndID should be a userID in table user. I…
user1765876
12
votes
2 answers

Foreign key vs check constraint for integrity

I am building a system that is a central repository for storing data from a number of other systems. A sync process is required to update the central repository when the other systems data is updated. There will be a sync_action table to identify…
Jeremy Power
  • 161
  • 1
  • 7
11
votes
3 answers

Simple constraint to limit an Oracle table to a single row

I'm looking for a simple way to limit an application configuration table to 1 row. I know I can do this with a virtual column containing a subquery, or a materialized view on top, but really it's a simple table I'd like a constraint that doesn't…
orbfish
  • 7,381
  • 14
  • 58
  • 75
11
votes
2 answers

INSTEAD OF Triggers and CASCADE paths

Let's say I have 3 tables in a hierarchy: TableA -> TableB -> TableC TableC has a foreign key relationship with TableB, and TableB has a foreign key relationship with TableA. If i delete a record in TableA, it should cascade delete down through the…
Brett Postin
  • 11,215
  • 10
  • 60
  • 95
10
votes
2 answers

Delete parent if it's not referenced by any other child

I have an example situation: parent table has a column named id, referenced in child table as a foreign key. When deleting a child row, how to delete the parent as well if it's not referenced by any other child?
zeroDivisible
  • 4,041
  • 8
  • 40
  • 62
9
votes
2 answers

mysql disable referential integrity

I want to drop all the schemas in a mysql db, and I don't want to be bothered with referential integrity errors in h2 it's done like this SET REFERENTIAL_INTEGRITY FALSE; drop table if exists company; drop table if exists computer; SET…
opensas
  • 60,462
  • 79
  • 252
  • 386
9
votes
7 answers

Referential Data Integrity: Necessity, nice-to-have, or old hat?

Frameworks like Rails have encouraged moving a lot of the logic, even stuff like constraints and foreign keys, off the database - in my opinion. for the better, as it's more manageable and easy to change. Even so, some operations are easier faster,…
Scott Schulthess
  • 2,853
  • 2
  • 27
  • 35
9
votes
1 answer

Query to find all FK constraints, and their referenced table columns

I have a large SQL 2012 database (100 tables), in which I need to find all constraints where the referential action is set to CASCADE (update or delete). In this useful answer I see that I can list the constraints using the following T-SQL (adapted…
EvilDr
  • 8,943
  • 14
  • 73
  • 133
1
2
3
…
23 24