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

Check for referential integrity break

In my process, I do something like: SET FOREIGN_KEY_CHECKS = 0; LOAD DATA INFILE '/path/to/mytable.txt' INTO TABLE mytable; SET FOREIGN_KEY_CHECKS = 1; Now, I need to check that the data after this import is not breaking the referential integrity.…
Alban Soupper
  • 671
  • 1
  • 5
  • 20
3
votes
1 answer

Consolidate multiple MySQL databases into one preserving referential integrity

I'm wanting to consolidate numerous MySQL databases, all with the same schema definition, into a single database. If I have a dump file from each database, how will I import them all into the same database without their primary keys and foreign keys…
jondow
  • 262
  • 3
  • 12
3
votes
2 answers

Are there tools for Rails to validate referential integrity of the database?

Applications have bugs or get bugs when updated, some hidden that they get detected months or years later, producing orphaned records, keys pointing nowhere etc. even with proper test suites. Allthough Rails doesn't enforce referential integrity on…
robokopp
  • 206
  • 2
  • 3
3
votes
4 answers

Removal of foreign key constraints, Referential integrity and Hibernate

My colleague mentioned that our client DBA proposed the removal of all foreign key constraints in our project Oracle DB schema. Initially I did not agree with the decision. I am a developer not a DBA. So later realized that there could be some…
Sujee
  • 4,985
  • 6
  • 31
  • 37
3
votes
1 answer

Showing MYSQL table columns with key types and reference

I need a query (INFORMATION_SCHEMA) which will for given schema and table name show me all table columns with following attributes (what key type it is: PK=>Primary Key, UQ=>Unique Key, FK=>Foreign Key, what is key name, and if it is Foreign Key…
sbrbot
  • 6,169
  • 6
  • 43
  • 74
3
votes
3 answers

Cross table constraints in PostgreSQL

Using PostgreSQL 9.2.4, I have a table users with a 1:many relation to the table user_roles. The users table stores both employees and other kinds of users. Table "public.users" Column | …
Dag Høidahl
  • 7,873
  • 8
  • 53
  • 66
3
votes
7 answers

Modeling many-to-one with constraints?

I'm attempting to create a database model for movie classifications, where each movie could have a single classification from each of one of multiple rating systems (e.g. BBFC, MPAA). This is the current design, with all implied PKs and FKs: TABLE…
Greg Beech
  • 133,383
  • 43
  • 204
  • 250
3
votes
1 answer

Create two foreign key references to same table with unique composite index to one of them

All items in the items table have a category that can be derived from the item_category table. +---------------------------------------------+ | items | +---------------------------------------------+ | id …
James Stott
  • 2,954
  • 2
  • 14
  • 15
3
votes
1 answer

Entity Framework disconnected graph and navigation property

I'm working with Entity Framework 6 Code First and Fluent API. I have a one to many relationship between Visit and VisitPage (1 Visit has many VisitPage objects). This is the simplified version of the POCO classes: Visit: Id (identity), UrlReferrer,…
Francisco Goldenstein
  • 13,299
  • 7
  • 58
  • 74
3
votes
3 answers

How can I best maintain integrity between two columns in a table?

Hypothetically, I have an ENUM column named Category, and an ENUM column named Subcategory. I will sometimes want to SELECT on Category alone, which is why they are split out. CREATE TABLE `Bonza` ( `EventId` INT UNSIGNED NOT NULL…
Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
3
votes
4 answers

SQL2005: Linking a table to multiple tables and retaining Ref Integrity?

Here is a simplification of my database: Table: Property Fields: ID, Address Table: Quote Fields: ID, PropertyID, BespokeQuoteFields... Table: Job Fields: ID, PropertyID, BespokeJobFields... Then we have other tables that relate to the Quote and…
littlecharva
  • 4,224
  • 8
  • 45
  • 52
2
votes
1 answer

Access: impose referential integrity on a linked table

I have two Access databases: Main stores most of my data and Memos stores data of datatype Memo. I am storing the memos in a separate db because everything I read about Memo fields said they were prone to corruption and that the only safe way to…
sigil
  • 9,370
  • 40
  • 119
  • 199
2
votes
1 answer

How to design MongoDB document for the hierarchical user flow?

Let us assume the following as the user flow in a multi tenant application with each tenant having its own set of catalogs or share a common catalog. User logs into the application and see UI with list of catalogs - User clicks on one catalog and…
Gopi
  • 5,656
  • 22
  • 80
  • 146
2
votes
2 answers

Referential Integrity - how to configure SQLAlchemy?

Can someone help me get the big picture when it comes to configuring SQLAlchemy models, so referential integrity is ensured at all levels? Going by the idea that referential integrity should be expressed and enforced by the DB, I have created a…
herira
  • 149
  • 3
  • 11
2
votes
2 answers

How can a relational database with foreign key constraints ingest data that may be in the wrong order?

The database is ingesting data from a stream, and all the rows needed to satisfy a foreign key constraint may be late or never arrive. This can likely be accomplished by using another datastore, one without foreign key constraints, and then when all…
user377628
1 2 3
…
23 24