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

Referential Integrity Constraint violation when attempting to set a FK to null

I am trying to update an entity in EF6. I have read that if I wish to change a ForeignKey property, I have to then ensure the Navigation Property is the correct one, or set it to null. I have taken the set to null approach, but I still receive the…
Simon
  • 9,197
  • 13
  • 72
  • 115
9
votes
1 answer

Does SQLite support referential integrity?

I have a Django web-server configured to use SQLite. In a many-to-many relationship (with extra fields), Django forces me to use the relationship model to establish a relationship between two objects. But I'm able to create a relationship between…
mynk
  • 1,194
  • 2
  • 13
  • 16
8
votes
1 answer

In MySQL, with FKs what's "CONSTRAINT" do?

I've looked at the MySQL 5.1 docs, and still can't figured this out -- that being I noticed a difference between the code I input into MySQL and output code by the system. What is the difference between the code sample 01 and 02, meaning 02 has…
blunders
  • 3,619
  • 10
  • 43
  • 65
8
votes
5 answers

Does putting integrity constraints decrease performance?

In a discussion with a friend, I got to hear two things - Using constraints causes slight decrease in performance. eg. Consider a uniqueness constraint. Before insertion, DBMS would have to check for the uniqueness in all of existing data, thus…
Amit Tomar
  • 4,800
  • 6
  • 50
  • 83
8
votes
1 answer

Compound foreign key with nullable column

In the following table, is there a way to ensure PreviousID always references an ID in a row with a matching ParentID, or, if ParentID is null, it is also null in the referenced row? CREATE TABLE MyTable ( ID int not null identity(1,1) primary…
Daniel
  • 47,404
  • 11
  • 101
  • 179
8
votes
3 answers

PostgreSQL: deleting rows referenced from another table

I have two tables, object and object_data, with object referencing object_data by foreign key (the relation is 1:1). For a set of objects, I need to null their object_data references and delete the corresponding object_data rows, like this: DELETE…
laurt
  • 1,811
  • 3
  • 15
  • 18
7
votes
5 answers

SQL Server foreign key to multiple tables

I have the following database schema: members_company1(id, name, ...); members_company2(id, name, ...); profiles(memberid, membertypeid, ...); membertypes(id, name, ...) [ { id : 1, name : 'company1', ... }, { id : 2, name :…
Maksim Vi.
  • 9,107
  • 12
  • 59
  • 85
6
votes
1 answer

MYSQL join tables multiple times

I have a table, with rows of events, and each one has (amongst lots of other fields) addedbyuser, editedbyuser, deletedbyuser There are INT, and refer back to the users table to a particular user. I am able to join one of the fields (say…
elef
  • 61
  • 2
6
votes
3 answers

Foreign key constraints involving multiple tables

I have the following scenario in a Postgres 9.3 database: Tables B and C reference Table A. Table C has an optional field that references table B. I would like to ensure that for each row of table C that references table B, c.b.a = c.a. That is,…
Reinstate Monica
  • 2,420
  • 14
  • 23
6
votes
3 answers

How to deal with mutually dependent inserts

I have a model that defines mutually recursive tables: Answer questionId QuestionId text Question text correct AnswerId What do I need to do to actually insert a question? I need to know what the correct answer is first. But to insert an…
nomen
  • 3,626
  • 2
  • 23
  • 40
5
votes
2 answers

How to reference other tables in check constraints?

I have a table, ProductSupportArticles: ProductSupportArticleID int NOT NULL ParentArticleID int NULL ProductID int NOT NULL Title varchar(100) NOT NULL Content varchar(MAX) NOT NULL ProductID is a foreign key to Products.ID,…
Jake Petroules
  • 23,472
  • 35
  • 144
  • 225
5
votes
2 answers

CQRS/DDD: Checking referential integrity

Should a command handler also check for referential integrity? This FAQ suggest that you shouldn't check this in the aggregates (http://cqrs.nu/Faq). Isn't checking that something exists part of the validation? For example you can have an…
Valderann
  • 805
  • 12
  • 30
5
votes
3 answers

Referential integrity in a relational data warehouse. Is it worth it? and what are the alternatives?

If you had to build a relational data warehouse of biblical proportions using SQL Server 2008, would you use foreign keys to enforce data integrity, or would you use some other means? I like foreign keys because you only need to get them right once,…
David
  • 51
  • 2
5
votes
1 answer

SQL constraint: two attributes, at least one foreign key match on same table

I have a table of phone numbers owned by a company, and a table of phone call records. Every call record includes (non-null) source and destination numbers. I am given the integrity constraint that either the source number or the destination number,…
Mike Embick
  • 77
  • 1
  • 7
5
votes
2 answers

referential integrity in Rails

Rails does not support referential integrity foreign keys. How do I manage this? Ideally the app should not have to deal with this; the database should. Do plugins like Foreigner have shortcomings? How is this dealt with?
akula1001
  • 4,576
  • 12
  • 43
  • 56
1 2
3
…
23 24