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
1
vote
2 answers

"Reference counting" trigger in PostgreSQL

Given a postgreSQL query with the following tables: +--------------------+ |Foo | +--------------------+ | id | value1 | refe | +--------------------+ +--------------------+ |Bar | +--------------------+ | id |…
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
1
vote
1 answer

Data Archiving [Design]

I am developing an archive module for an application using Dotnet and SQL Server as back end. From multiple approaches of archiving we've decided to build a custom application to archive the complete database up-to a chosen threshold to another…
S M Kamran
  • 4,423
  • 7
  • 25
  • 35
1
vote
1 answer

Referential integrity access

I have two tables in access database. First table is named Program, its fields are Program ID and Program Name, the Program ID is the primary key for this table. The second table is named Partner, its fields are Partner ID and Partner Name, the…
Ksagar
  • 25
  • 1
  • 5
1
vote
1 answer

Fixtures Plugin Referential Integrity Exceptions In Grails

I have an object Foo that has a bidirectional one-to-one relationship with Bar and another one with Baz. When I try to do a .load with Foo and only give it a Bar, I get referential integrity exceptions complaining that there isn't a Baz. Should this…
Alex Beardsley
  • 20,988
  • 15
  • 52
  • 67
1
vote
0 answers

Enforcing foreign key "group" integrity in tables without creating redundancy

(I'm using SQL Server 2008 R2 but I'm not sure that's important) If I have the following "base" tables: SettingTypeCategory - SettingTypeCategoryId (PK) - CategoryName SettingType - SettingTypeId (PK) - SettingTypeCategoryId (FK) -…
Rob
  • 10,004
  • 5
  • 61
  • 91
1
vote
1 answer

Foreign key matching in PostgreSQL

Just curious, if I have this table: CREATE TABLE "post" ( "id" SERIAL, "revision" INTEGER NOT NULL DEFAULT 0, "summary" CHARACTER VARYING NOT NULL, "description" TEXT NOT NULL, "user_id" INTEGER NOT NULL REFERENCES "user"…
Jeffrey04
  • 6,138
  • 12
  • 45
  • 68
1
vote
1 answer

Is there a way to check referential integrity for MyIsam tables using YII native relations?

While I used InnoDB tables I caught FK constraint exceptions from InnoDB engine. I know that Yii can fetch related tables according to relations(). Besides I need to check referential integrity during other CRUD operations - such as insert, update…
Peter Lyashevich
  • 35
  • 1
  • 3
1
vote
0 answers

Teradata 12.0: Referential Integrity Constraint

Is it possible to add a referential integrity constraint on a column which references a non primary-key column in another table. I basically have a table with an ID column which is non-unique. I have another table with REFERENCE column which refers…
Zeeshan Arif
  • 467
  • 4
  • 14
1
vote
1 answer

MySQL, referential integrity doesn't work

I use MySQL Server 5.5; MySQL Workbench 5.2 CE; WinXp SP3; I created 2 tables: CREATE TABLE IF NOT EXISTS `mydb`.`Address` ( `AddressID` INT NOT NULL AUTO_INCREMENT , `Country` VARCHAR(45) NOT NULL , `City` VARCHAR(45) NOT NULL , `Region`…
Sergey Chepurnov
  • 1,397
  • 14
  • 23
1
vote
1 answer

MS Access with referential integrity not allowing NULL values

There are two tables in a Microsoft Access database. One of them defines elements, the other one references them. This reference is however optional. The value can be NULL. As long as the foreign key exists, I am not allowed to insert or update the…
ygoe
  • 18,655
  • 23
  • 113
  • 210
1
vote
7 answers

Handling Deleted data in applications

Assume that you are writing a simple app. The model is that a 'project' has a 'category'. The project's category can be edited by choosing from a drop down list that contains all possible categories. The user creates the following Categories: C1,…
HBD
1
vote
2 answers

Related insert fails in transationscope

I am using TransactionScope to add a object's data to one database. pseudo code: using (TransactionScope trx = new TransactionScope()) { SqlConnection con = DL.GetNewConn(); int newParentRecordID = InsertParentIntoTableA(object parent, con); …
callisto
  • 4,921
  • 11
  • 51
  • 92
1
vote
1 answer

MySql - can BEFORE INSERT TRIGGER insert into 2 columns?

Can this trigger be changed so that the sortorder table gets 2 column values (sortOrderId, sortOrder) inserted? How is the value of sortOrder found? If it is known and can be inserted into image table then can it also be inserted into the sortorder…
JoJo
  • 4,643
  • 9
  • 42
  • 65
1
vote
1 answer

Merging identical tables but maintaining separate referential integrity

Consider a dimensional model with fact tables like (fk_dim1value, fk_dim2value, ..., value) where the fk_X columns are foreign keys into corresponding trivial dimension tables dim1value (id, value), dim2value (id, value), etc. These…
Gunnlaugur Briem
  • 2,684
  • 2
  • 23
  • 24
1
vote
4 answers

How do I check that I removed required data only?

I have a really big database (running on PostgreSQL) containing a lot of tables with sophisticated relations between them (foreign keys, on delete cascade and so on). I need remove some data from a number of tables, but I'm not sure what amount of…
Pavel
  • 163
  • 7
1 2 3
…
23 24