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

Referential completeness integrity check in SQL

I have 3 tables - users, teams and team_members. The latter is a many-to-many map from team(id) to user(id) (foreign keys to teams and users respectively). Is there any integrity check I can add to my database that can assert that while teams with…
pathikrit
  • 32,469
  • 37
  • 142
  • 221
2
votes
1 answer

Best way to ensure referential integrity

I'm a SQL noob, and whilst I'm aware of the major tools available, I'm not experienced enough to know the best tool for certain situations. As an example, I current have a group of tables where referential integrity is needed. Each table does not…
dotnetnoob
  • 10,783
  • 20
  • 57
  • 103
2
votes
1 answer

PostgreSQL import, if constraint not met set null

I'm trying to import a db from file-maker pro into PostgreSQL. As a result of not being maintained well, the data has had some degradation in links between tables over time. I've attempted to import the data with no constraints and then add the…
ChadJPetersen
  • 383
  • 3
  • 17
2
votes
2 answers

How to find records that violates foreign key constraints

I'm trying to find records that is violating the ORA-02291: integrity constraint:: I was running this query, But I didnt get any results back:: SELECT child.parent_id FROM child LEFT JOIN parent ON child.parent_id = parent.parent_id …
amateur
  • 941
  • 4
  • 22
  • 33
2
votes
1 answer

Enforce maximum number of child rows

If a database has a pair of tables in a typical "parent and child" fashion, is there a way to enforce (without using triggers) that each parent can only have a maximum, say, of four children? So we have a Parents table: create table dbo.Parents ( …
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
2
votes
1 answer

'Too many indexes' error when creating relationship with referential integrity in Access 2010

I have a database that I believe is normalized. There is a tables called SAMPLES which contains sampleID as the primary key. I have 29 tables related to SAMPLES each of which has sampleID as a foreign key. All of these relationships are one to…
James
  • 479
  • 4
  • 4
2
votes
2 answers

How to change the memberof overlay's objectClass to groupOfUniqueNames

I am running OpenLDAP 2.4.31. Based on Reverse Group Membership Maintenance: The memberof overlay updates an attribute (by default memberOf) whenever changes occur to the membership attribute (by default member) of entries of the objectclass (by…
Daniel Szalay
  • 4,041
  • 12
  • 57
  • 103
2
votes
4 answers

Efficient ways of counting the number of descendant records

Consider this tree-like table structure: CREATE TABLE nodes( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, parent INTEGER, descendant_count INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(parent) REFERENCES nodes(id) ON DELETE…
nice ass
  • 16,471
  • 7
  • 50
  • 89
2
votes
2 answers

How should I keep referential integrity to users in a separate membership provider?

If I have a separate membership provider API, which doesn't store credentials and roles in my database, how should I maintain referential integrity with my application's reference to users? For example, we interface with the membership API but…
Matthew
  • 10,244
  • 5
  • 49
  • 104
2
votes
2 answers

Obtain referential integrity at the expense of 2NF- is it a reasonable trade off?

Consider the following two tables: Table A: [K1, K2, PropA] Table B: [K3, PropB] The primary key of Table A is composite [K1,K2]. The primary key of table B is K3. Table has an inclusive dependency on on table B- values in K3 have to be matched…
Vitaliy
  • 8,044
  • 7
  • 38
  • 66
2
votes
1 answer

How to empty all tables in Apache Derby?

I want to empty all tables in Apache Derby. I do not want to remove all the tables, just their contents. This cannot be done easily with a simple set of DELETE statements, as the tables can contain circular references. In PostgreSQL/H2/Oracle, you…
Rob Audenaerde
  • 19,195
  • 10
  • 76
  • 121
2
votes
3 answers

SQL Server relationships buried in stored procedures rather than schema

At present we have very little referential integrity, as well as having a number of tables that self-join (and indeed would perhaps better be represented as separate tables or views that joined). The knowledge of how these tables relate to each…
Paul McC
  • 21
  • 2
2
votes
2 answers

Referential integrity error when using infile, but no error when using mysql CLI

When I try loading a file of CSV data using the --local-infile option, I get the following error: ERROR 1452 (23000) at line 2: Cannot add or update a child row: a foreign key constraint fails (realtax.city_desc, CONSTRAINT city_desc_ibfk_1 FOREIGN…
A A
  • 33
  • 4
2
votes
1 answer

how to import a table with a self relationship

I have the following table: EntityId - PK Label ParentEntityId - FK ParentEntityId is joined to EntityId of the same table. now I am having problems importing data to this table using SSIS because of instances where the parent entities haven't…
Mel
  • 3,058
  • 4
  • 26
  • 40
2
votes
3 answers

Converting Pseudocode to SQL Script

So I have two tables: Bookmarks has a few columns [id, etc.] Person_Bookmark has 2 columns [personId, bookmarkId] Bookmarks represents links to other websites. All valid bookmarks have an id. The Person_Bookmark table has a bunch of personIds and…
Kevin Meredith
  • 41,036
  • 63
  • 209
  • 384
1 2 3
…
23 24