Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [referential-integrity]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
328 views

SQL - referential integrity - Foreign key and Check constraint

Please let me know if below two declarations are same. create table R1 ( a INT PRIMARY KEY, b INT references K(w)); Note: K is a table with single attribute w as primary key create table R1 ( a ...
0
votes
1answer
101 views

Foreign key constraints check during replication?

I am a little bit new to replication and can't seem to find an answer to this anywhere. Let's say I have a simple relation in my database: Department table with Code column as PK and Person table ...
1
vote
1answer
22 views

two-table-over <+> functional relationship: maintaining referential integrity

I have three tables, the last two having a many to one relationship with the first, written here in SQL: create table study ( id bigint primary key, name text ); create table treatment ( ...
1
vote
1answer
31 views

Data Deplucation and relationship integrity [closed]

I apologize in advance as this question is likely to be more on the theoretical side, with (hopefully) a fair bit of discussion. This is the scenario: We have Salesforce and a multiple local ...
2
votes
1answer
53 views

Hierarchical references abstraction with strict model

I want to abstract a real world hierarchy in a strict DB model (as strict as possible). My problem is to to create a structure with a this-or-this-but-not-both rules set. I have two entity types, ...
4
votes
1answer
121 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 ...
1
vote
1answer
46 views

How to delete foreign key with lots of references

Table B have several thousand records that references table A with a foreign key. The application sends a request to delete the row from table A. It's important that the synchronous operation would ...
1
vote
0answers
21 views

Referential integrity based on data more than a table away

Notes on tables A, B, C, D, E B.aid and C.aid reference A.aid. D.cid references C.cid and D.did is the primary key. D is a C. cid and did are pre-assigned and I cannot change them (they need to ...
5
votes
5answers
1k 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 ...
0
votes
0answers
14 views

More than one path between M-M tables

I'm trying to figure out if it is bad practice to have more than one path between tables that are related via many-to-many relationships. I have 3 user-facing tables: Dupe, Cue, and Take. There is a ...
0
votes
2answers
40 views

How to model very flexible referencing relationships between many tables?

I have various different entity types in my database. For purposes of example, consider an IMDB database: Movie Person Actor Director Translation and 20 more tables My application enables free-form ...
29
votes
2answers
26k views

Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

I've noticed a MATCH SIMPLE and MATCH FULL, but I don't understand what they do. I see default is MATCH SIMPLE; but, how do the other MATCH clauses to the FOREIGN KEY constraint function?
7
votes
4answers
16k 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......
9
votes
1answer
3k 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 ...
5
votes
1answer
473 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 ...
3
votes
1answer
410 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. ...
2
votes
1answer
2k views

Relationships between subtypes of an entity

I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events). Each row is identified by an id (obj_id) and has an ...
1
vote
0answers
22 views

Redundant column to maintain data integrity

Consider the following example: Syllabus -id -course_id FOREIGN KEY (course_id) REFERENCES Course(id) SyllabusItem -id -syllabus_id FOREIGN KEY (syllabus_id) REFERENCES Syllabus(id) ...
-1
votes
1answer
27 views

Keeping reference in an array format

I have a design with an orders table and products table. Each order can have many products, so 1 order N products. The default design would be to have something like this: orders table order_id, ...
9
votes
4answers
37k 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 ...
2
votes
2answers
65 views

Enforcing existence of records while avoiding circular references

I'm having issues designing a satisfactory solution for the following problem. There are two main entities: Customer User A customer can have many users associated to it. A user can be associated ...
5
votes
3answers
75 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 ...
0
votes
1answer
106 views

FK to table with non-unique values via a on commit refreshing materialized view?

I would like to create a FK to a table without unique values. So I cannot create a simple FK. [to clarify, the parent table has duplicates - dont ask why, not in my control ] I tried creating a ...
12
votes
2answers
8k 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 ...
2
votes
2answers
574 views

Conditional table reference

I have a table tree: +------+-------+ | id | name | +------+-------+ | 0 | tree1 | | 1 | tree2 | | 2 | tree3 | | 3 | tree4 | +------+-------+ A table pen: +------+------+ | id | ...
22
votes
5answers
6k 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(...
3
votes
0answers
151 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 ...
2
votes
1answer
68 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 ...
1
vote
1answer
24 views

Modelling a second level table relationship

I have three tables in my Database: Customers ---------- Id Name Addresses ---------- Id Description CustomerId Orders ---------- Id Description CustomerId DeliveryAddressId I'd like to make ...
27
votes
3answers
18k 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 ...
5
votes
2answers
965 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 ...
1
vote
3answers
155 views

Determinism inside an INSTEAD OF trigger

In reference to my prior question about a preferred design to avoid circular or multiple update path referential integrity cascading updates and deletes via ON UPDATE CASCADE and ON DELETE CASCADE, I'...
5
votes
2answers
3k 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 ...
1
vote
4answers
253 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) | ...
1
vote
1answer
613 views

How do I set up primary and foreign keys referencing two different tables?

I need to have two primary keys also to be the foreign keys in the same table. These two keys are referenced in different tables. I keep getting error messages that are unclear to me. My code is as ...
1
vote
1answer
64 views

Is there another source for untrusted foreign keys beside FK disable and BULK inserts?

This question and its answer explain how bulk inserts will render FK as untrusted, as bulk inserts are not completely checked (only PK and UNIQUEs) . I also know that temporarily disable of a FK may ...
8
votes
2answers
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 ...
16
votes
2answers
13k 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 ...
3
votes
2answers
4k 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 ...
1
vote
2answers
936 views

Deleting would-be orphans when creating FK in Postgres

I am working on a script that replicates schema and data. I first create all the structure except for foreign keys after reading it from the source's system tables, then copy the data, then create the ...
0
votes
1answer
290 views

Check integrity between servers

I have three Database servers, running PostgreSQL 9.2: master01 (Own data center USA) slave01 (Own data center USA) slave02 (AWS Australia) Both slaves use streaming replication + ...
0
votes
1answer
139 views

Simple way to list order of referential integrity in Postgres

Is there a clean, simple, and preferably prepackaged (ready function?) way to list the order of referential integrity in a Postgres database, rather than trying to read the system tables to find that ...
0
votes
1answer
394 views

How do triggers and check constraints relate to maitaining database rules

I was looking at a tutorial on triggers for SQLite. The tutorial said that a use of triggers could be for data validation. The following code was given as an example: CREATE TRIGGER ...
1
vote
1answer
586 views

Make a boolean column unique per group

I want to ensure consistency and make sure only one phone line is the default for a house. Schema: PhoneLine ID | isDefault | HouseID I want to add a trigger so that when you add/update a row, and ...
24
votes
3answers
5k 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 ...
5
votes
3answers
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 ...
1
vote
2answers
249 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` ( `...
31
votes
15answers
22k 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 ...
2
votes
1answer
255 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, ...
4
votes
1answer
6k 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 ( ...