Skip to main content

Questions tagged [referential-integrity]

Facilities provided by a database management system to ensure consistency within the data.

Filter by
Sorted by
Tagged with
59 votes
2 answers
58k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
user avatar
37 votes
2 answers
41k views

Constraint - one boolean row is true, all other rows false

I have a column: standard BOOLEAN NOT NULL I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with ...
theGtknerd's user avatar
33 votes
16 answers
27k views

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
32 votes
3 answers
36k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
user2965107's user avatar
31 votes
3 answers
15k views

Constraint to enforce "at least one" or "exactly one" in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address | ...
Kevin Burke's user avatar
26 votes
5 answers
8k views

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like: CREATE TABLE Book ( BookID INT NOT NULL , BookTitle VARCHAR(...
ypercubeᵀᴹ's user avatar
20 votes
3 answers
77k views

Cascade primary key update to all referencing foreign keys

Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ? # EDIT 1: When I run followinq query select * from sys.foreign_keys where ...
mounaim's user avatar
  • 659
17 votes
2 answers
19k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
Raiden Freeman's user avatar
15 votes
1 answer
8k views

Enforcing constraints "two tables away"

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short for ...
Snowball's user avatar
  • 327
14 votes
2 answers
9k views

Do I need a separate Id column for this "mapping" table?

I have a table of Producers and a table of Products, both of which are of the form: Id - int, Primary key Name - nvarchar A Producer can carry multiple Products, so I was going to create a table ...
Josh Darnell's user avatar
  • 29.4k
13 votes
3 answers
13k views

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it. quote from MySQL 5.7 Reference Manual / GRANT Syntax REFERENCES ...
Sybil's user avatar
  • 2,518
11 votes
4 answers
78k views

DELETE statement conflicted with the REFERENCE constraint

My situation looks like this: Table STOCK_ARTICLES: ID *[PK]* OTHER_DB_ID ITEM_NAME Table LOCATION: ID *[PK]* LOCATION_NAME Table WORK_PLACE: ID *[PK]* WORKPLACE_NAME Table INVENTORY_ITEMS: ID ...
derwodaso's user avatar
  • 111
8 votes
1 answer
10k views

If foreign keys/cascade deletes are bad, why use a database-server with that feature?

I noticed wordpress/rails etc do not use foreign keys constraint or cascade deletes features from database. Instead they handle this in PHP/Ruby/scripting level! I have read this and this. Most ...
rahul286's user avatar
  • 215
8 votes
2 answers
6k views

"Merge" two rows in a Postgres table, with foreign keys

I am keeping a database of books I've read, using the following two tables in PostgreSQL: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL ...
Steve D's user avatar
  • 195
7 votes
2 answers
1k views

Is having relationships between separate databases bad practice?

I am working with a client that has multiple databases. There are several master level databases that have relationships back to them from instance level databases (application specific DBs). The ...
DDiVita's user avatar
  • 173
7 votes
4 answers
19k views

How do I identify tables that have a foreign key to a specific table in Sybase?

I am looking for (preferably) an SQL statement that selects the table/and column names for any table with a foreign key to a given table in Sybase. I think it should be somehow possible with the sys......
René Nyffenegger's user avatar
7 votes
2 answers
4k views

Foreign key without referenced columns specification

I noticed in the SQL Server documentation that the list of referenced columns is not a required parameter of a foreign key constraint: <column_constraint> ::= [ CONSTRAINT constraint_name ]...
discrete's user avatar
  • 179
7 votes
1 answer
331 views

Do any of the graph based/aware databases have good mechanisms for maintaining referential integrity?

Do any of the graph-based/graph-aware databases (Neo4j, ArangoDB, OrientDB, or other) have mechanisms for maintaining referential integrity on a par with those offered by relational databases? I'm ...
pvgoran's user avatar
  • 171
7 votes
2 answers
1k views

Foreign key to multiple tables via forwarding table

Problem statement I have a table with a column whose values are foreign keys, but the target table of the foreign key differs from row to row. The relevant table can be determined from the key value ...
wchargin's user avatar
  • 171
6 votes
3 answers
6k views

Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source. tickets - id - sutff lookup - ...
Phill Pafford's user avatar
6 votes
1 answer
5k views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
romaia's user avatar
  • 206
6 votes
2 answers
314 views

How should an object that can reference an object of multiple types be modeled in a relational schema?

Apologies for the obscure question, it may make more sense with a concrete example: In my application, I may create a portfolio that contains a set of projects. However, I may also add a 'sub-...
JKomusin's user avatar
  • 163
5 votes
5 answers
3k views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
Agi Hammerthief's user avatar
5 votes
1 answer
684 views

Does SQL manage foreign key constraints in terms of insertion?

Say I have a table tbl1 with data A1 and A2, where A1 is the primary key. Then I have another table tbl2 with data A3 and A1, both together being the primary key, referencing A1 as a foreign key from ...
Justin's user avatar
  • 153
5 votes
1 answer
532 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
Snowball's user avatar
  • 327
5 votes
2 answers
1k views

Help with good RDBMS transactional schema design that mimicks a sports league

I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are: Well normalized Transactional Application ...
maple_shaft's user avatar
5 votes
3 answers
4k views

SELECT + INSERT or INSERT alone

I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second. The first time a ...
user313631's user avatar
5 votes
3 answers
88 views

Constraining data based on related data in a different table. Should this be done on the database level or the application level?

I am working on a database that contains our customer information as well as information about our vendors. Part of that includes matching our customers with our vendors, the account numbers those ...
Zachary Weber's user avatar
5 votes
2 answers
2k views

Preferred design to avoid circular/multiple update paths

Consider the following design: CREATE TABLE dbo.Farmers ( FarmerName varchar(10) NOT NULL PRIMARY KEY ); CREATE TABLE dbo.FarmEquipment ( FarmEquipmentName varchar(10) NOT NULL ...
Hannah Vernon's user avatar
  • 70.2k
5 votes
2 answers
155 views

Relationship that are optionally more specific

Forgive my title, I couldn't think of anything that accurately describes what I'm talking about. I currently have the following relationship. CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT NULL,...
Matthew's user avatar
  • 185
4 votes
3 answers
21k views

Truncate parent table in Oracle when child table is empty

Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in ORA-02449: unique/primary keys in ...
Isaac Kleinman's user avatar
4 votes
3 answers
574 views

Inconsistency in Relational Databases

I generally get confused when the term consistency is used. NoSQL tutorials always refer to the reads whereas Relational tutorials refer to a consistent state (ensuring referential integrity ...
vvs14's user avatar
  • 161
4 votes
3 answers
1k views

How to maintain referential integrity of an object graph when a node is deleted

In a relational database, if a node in an object graph is deleted, how can the object graph's referential integrity be maintained? For example: A product is deleted, how is the invoice containing ...
Travis J's user avatar
  • 143
4 votes
3 answers
3k views

How to select a subset of data from a large ER model?

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
jrara's user avatar
  • 5,333
4 votes
1 answer
2k views

Complex Delete Cascade Including Self-Referencing Table

Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I ...
Keith Stein's user avatar
4 votes
1 answer
12k views

Foreign key for array column [duplicate]

I have a table: CREATE TABLE methods ( method_id serial PRIMARY KEY, method_name varchar(100) ); I now want to create a table with the following columns: CREATE TABLE experiments ( ...
Valentin_Ștefan's user avatar
4 votes
3 answers
981 views

Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?

I have a requirement for a very large (in terms of the number of rows, not data) database, and need to use a very long, unique, hexadecimal PRIMARY KEY instead of the usual AUTO_INCREMENT integer. As ...
Deele's user avatar
  • 143
4 votes
2 answers
144 views

Table design when one data is dependant of another and the second one has repeated values

I have a products, and subproducts. Like: Product Identifier - Product Name - Subproduct Identifier - SubProduct Name 1 ProA 1 SubProA1 ...
jsedano's user avatar
  • 143
3 votes
2 answers
849 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
max's user avatar
  • 141
3 votes
2 answers
392 views

Referential Integrity - Indirect Foreign Key In "Depth"

Question What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship ...
Kirk Saunders's user avatar
3 votes
1 answer
276 views

Relationship with a default value

Firstly, I know my title is somewhat misleading, but I couldn't think of a better title. Here is my current relationship CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT NULL, Name NVARCHAR(...
Matthew's user avatar
  • 185
3 votes
1 answer
609 views

Optional Parent-Child relationship

I have a problem modeling a relationship whereby the child may or may not be related to a parent. For example. You have a Band database with a Tour(parent) table, and a Gig/Concert(child) table. Now, ...
vernon's user avatar
  • 31
3 votes
1 answer
50 views

Integrity Issues in this structure?

Imagine the situation. There are three tables, Student table[PK Student ID] Enrollment Table [PK EnrollID, FK Student ID] Fees Paid [PK Fee ID, FK EnrollID, FK StudentID] Edit: Fee_Paid Table ...
Niana 's user avatar
  • 131
3 votes
2 answers
1k views

For each row create a linked row in another table with auto-generated ID

In Postgres, I have an existing table: things thing_id thing_name destination_id 10 thing 10 null 15 thing 15 null For each row in that table, I want to add rows to two new related tables one of ...
Robert Elliot's user avatar
3 votes
2 answers
235 views

Create foreign key constraint on indirect data

I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database: CREATE TABLE logger ( id INTEGER PRIMARY KEY ); CREATE TABLE ...
t_over's user avatar
  • 31
3 votes
1 answer
1k views

What is the correct way to map relationships between structurally similar comments tables and commentable entities?

I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments. ...
john_h's user avatar
  • 33
3 votes
0 answers
518 views

Orphan records after delete statement in stored procedure

I have a database where some of the tables have parent --> child --> grandchild relationships through foreign keys as shown below. CREATE TABLE Parent ( ParentID INT UNSIGNED PRIMARY KEY ...
user3607758's user avatar
3 votes
0 answers
154 views

Visualize the effects of a cascading delete or update?

In MySQL, I'm working with a database that has integrity constraints on foreign keys (nothing overly special here). Is there a method to somehow visualize the effects of a cascading delete or update? ...
Jacco's user avatar
  • 131
2 votes
4 answers
675 views

Referential integrity when two FK columns need to match themselves in the same table?

It's possible my design is wrong, or there's simply a better way. I'll use a very simple example: --------- dbo.Book---------- | | | BookID int identity (1,1) | ...
user1447679's user avatar
2 votes
2 answers
940 views

DB Design. Tracking history without losing referential integrity

To make it simple, let’s say we have these 2 tables: CREATE TABLE `tbl_companies` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `tbl_workers` ( `...
DandyCC's user avatar
  • 23