Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [foreign-key]

A type of integrity constraint used in a RDBMS platform to ensure that a value in a column matches one of a range of key values from another table.

149 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4 votes
1 answer
4k views

Update all foreign keys to a different primary key

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. Let'...
Jeremy Wadhams's user avatar
3 votes
0 answers
101 views

Why can't I combine CHECK constraint with ON UPDATE CASCADE?

Given table A: CREATE TABLE `A` ( `id` int NOT NULL, PRIMARY KEY (`id`) ); when I try to create this second table B: CREATE TABLE `B` ( `id` int NOT NULL, `val1` int, PRIMARY KEY (`id`), ...
Jonatan G's user avatar
3 votes
0 answers
227 views

Does MySQL always run foreign key constraint checks when a row is updated? Or only when a relevant column is changed?

I have this question and I haven't found specific documentation to confirm the behavior and am unaware of how to manually check this myself. Consider I have table A with b_id foreign key to table B. ...
user3509128's user avatar
3 votes
0 answers
174 views

Create a foreign-key-like constraints that checks something more generic than equality

There are some situations where I would like to set up some constraints in a table that depend on values in another table. For example, I can define two tables as: CREATE TABLE foo ( foo_id serial ...
user2233709's user avatar
3 votes
0 answers
3k views

postgresql handle conflict on delete

I have a table that looks roughly like this: create table people ( id bigserial primary key, name varchar, deleted timestamp with time zone, parent bigint references people(id) ...
samf's user avatar
  • 31
3 votes
1 answer
348 views

Table was deleted during ALTER, how to recover?

I tried to ALTER a table to add a FOREIGN KEY as: ALTER TABLE artists ADD FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE; and I received an error of ERROR 2013 (HY000): ...
Googlebot's user avatar
  • 4,531
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
2k views

Supertype table with disjoint subtypes in MySQL

I want to have base parent table with few disjoint subtypes. To illustrate what I mean: CREATE TABLE issues ( issue_id SERIAL PRIMARY_KEY, issue_type CHAR(1) CHECK (issue_type IN ('B', 'F')), ...
Vasek's user avatar
  • 31
3 votes
0 answers
49 views

If a relationship is (logically) deleted, is this marked in the link table or just the satellite table(s)?

I'm learning about the Data Vault data warehousing approach, and so far the examples I've seen have a DateLoaded or similar field in all tables (hub, link, and satellite), but a DateExpired or EndDate ...
Jon of All Trades's user avatar
3 votes
0 answers
202 views

Slowly changing dimensions - data integrity

When using SCD is it possible to still enforce FK contraints. Consider the following table structure: customer (data_version_id PK, customer_id PK, name, address1,...etc) order(order_id PK, ...
Neil Griffiths's user avatar
3 votes
2 answers
60 views

Reasons to maintain FKs on an empty, deprecated table?

So I have a few dozen empty tables with foreign keys to other, populated tables. These tables are not being accessed by any CRUD operations, but have about 6-12 months before they can be unlinked from ...
LowlyDBA - John M's user avatar
3 votes
1 answer
702 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not all)....
DLA2014's user avatar
  • 157
2 votes
0 answers
22 views

Is there an algorithmic way to identify potential primary key foreign key relationships in postgres

I have a lots of tables in postgres with a lots of columns per each table. I need to write a program in such a way that it can identify the columns that can be a potential primary and foreign key pair....
Ashwin Prasad's user avatar
2 votes
1 answer
74 views

Why does creating a compound unique index with an fk and text column fail for mariadb?

I encounter an error when trying to create a compound unique index using a foreign key and a TEXT column. The error can avoided by explicitly adding an index on the foreign key or changing the type of ...
joelliusp's user avatar
  • 121
2 votes
0 answers
24 views

Is this the best practice for separating all records by user company?

This is more of a general database question than specifically a Prisma question, but the implementation I have is written using Prisma connected to MySQL. Take the following part of my schema.prisma: ...
Basil's user avatar
  • 131
2 votes
2 answers
1k 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 ...
CuriousGeorge's user avatar
2 votes
0 answers
617 views

Query for getting reserved seats in a cinema

I have this cinema project for a course. And I have to visually show the seats to user so they can reserve their preferred seats. I need to get the reserved seats from the database of that particular ...
Ramsha Khalid's user avatar
2 votes
0 answers
56 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
xtratic's user avatar
  • 121
2 votes
0 answers
104 views

SQL Server database design with foreign keys

See the following link for the (total) databse design: https://sqldbm.com/Project/SQLServer/Share/_AUedvNutCEV2DGLJleUWA I am not used to the database design first and then build it. But... for ...
Patrick Rennings's user avatar
2 votes
1 answer
167 views

One status table for all models?

lets say I have a users,posts and categories tables .. users table would have statuses like ( active, expired .. etc ) posts table would have statuses like ( published, draft, archived .. etc ) ...
rook99's user avatar
  • 151
2 votes
0 answers
191 views

Should I move repeating foreign keys into separate table?

Idea Users can upload lots of items and send them from processing. From the upload settings I can set a channel to which the uploaded items belong to. Channel is a separate entity. Currently I set ...
Vaelyr's user avatar
  • 155
2 votes
0 answers
76 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. Activities ...
Willem-Aart's user avatar
2 votes
0 answers
386 views

Compound key that only allows nulls if they are null in the referenced table

I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the ...
Michael Underwood's user avatar
2 votes
0 answers
389 views

Why are foreign keys ignored on subscribers?

We are using merge replication on SQL Server 2008 R2. There are many tables with foreign keys providing a logical dependence on each other. For this question, let's say there are User and File tables, ...
George T's user avatar
  • 245
2 votes
0 answers
1k views

AFTER DELETE trigger will not fire during a cascade delete

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends. Aisle Table: Aisle char(4) Start_Node ...
user1935361's user avatar
2 votes
0 answers
58 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
dragonroot's user avatar
2 votes
1 answer
2k views

Does the order I declare foreign key constraints in matter?

Does it matter what order I add foreign key constraints to a table in, or what order I write them in a CREATE TABLE statement? MySQL clearly at least remembers the order; if I run the DDL statements ...
Mark Amery's user avatar
  • 1,075
1 vote
0 answers
81 views

Foreign key constraint wrongly gives the error in mariadb

We are using AWS RDS for mariadb. There are several tables out of which I am mentioning two which are causing the issue: Table 1: PRODUCT_OFFERING Table 2: CHARACTERISTIC Related by foreign key as ...
Arjun Lajpal's user avatar
1 vote
1 answer
841 views

Modeling Polymorphic Relations in Postgres 15 with bi-directional (cyclic) FK constraints

I am drawn to this design like a moth to a flame. I've read rumblings that cyclic foreign keys are a nightmare. Generally, I can see why they should be avoided. In this particular case, however, I don'...
untitled90's user avatar
1 vote
0 answers
60 views

Modify parent's and child's foreign key value of a record

Consider following relationship on MySQL database. +--------------+ +---------------+ | Child | | Parent | +--------------+ 1 +--------...
Salitha's user avatar
  • 111
1 vote
1 answer
147 views

Column containing keys from columns of another table

I have two csv files, countries and birth_rate_stat with the following data: ISO ISO3 ISO_CODE FIPS Display Name Currency Name Phone_Code Region Code AF AFG 4 AF Afghanistan Afghani 93 Asia AX ALA ...
Red Tornado's user avatar
1 vote
0 answers
316 views

Use 2 columns of a table as foreign key in an other table to avoid circular-reference

I have two tables: ObjectContainer containerID INT NOT NULL PRIMARY KEY mainObjectID INT NOT NULL FOREIGN KEY REFERENCES Object (objectID) Object objectID INT NOT ...
Xudla's user avatar
  • 11
1 vote
0 answers
511 views

DB Browser for SQLite foreign key errors

My Main Table is: CREATE TABLE "STUDENT" ( "last_name" TEXT NOT NULL, "first_name" TEXT NOT NULL, "date_of_birth" INTEGER NOT NULL, "...
mghee's user avatar
  • 11
1 vote
0 answers
62 views

Best Method for Modeling/Implementing Dependent Primary Child?

I’ve implemented Primary Child functionality using a third table as described by @ypercube here (and other posts). I chose this method because it’s database-agnostic, simple (no triggers or deferred ...
DoctorYes's user avatar
1 vote
0 answers
4k views

Foreign Key to Partitioned Table

I have the following basic schema for an ever growing huge transaction table. In order to query speedup & phase out stale data, I need to partition by time & country. CREATE TABLE transaction (...
eliangius's user avatar
  • 145
1 vote
0 answers
397 views

Drop Foreign Key that does not exist?

So I just tried to drop a foreign key constraint from a table which did not exist. Long story short, we had a script which looked for FK constraints from information_schema, it looked like this: ...
Xopherus's user avatar
  • 121
1 vote
0 answers
456 views

How can I share virtual key in DBeaver for other device?

As I know, the virtual key in DBeaver just existed on the client-side (local device), not in real database. I want to share this virtual key for other devices (not only my device). How can I do that?
Tuấn Anh's user avatar
1 vote
0 answers
171 views

Access ForeignKey columns via dot in PostgreSQL?

I have a table user with columns id, name, email I have a table book with columns id, name, author (ForeignKey to user). Is there a way to write this SQL query in PostgreSQL? select name, author....
guettli's user avatar
  • 1,521
1 vote
0 answers
93 views

Foreign key or copy of attributes in log entities

I am trying to design an ER for a system with the following specifications: Each user has an account The user can link his/her bank accounts to his/her account in our system The user can deposit or ...
fmatt's user avatar
  • 111
1 vote
0 answers
140 views

Difference between setting of a shared lock when using INSERT or UPDATE/DELETE statements

I would like to know why a shared lock is set when I use a foreign key on an INSERT statement, but not when I use a foreign key on an UPDATE or DELETE statement. Specifications: version: 10.1.47-...
Daan's user avatar
  • 123
1 vote
1 answer
467 views

Thousands foreign keys referenced to one table and other postgres scaling philosophy

I'm inspecting one legacy and quite big database which is growing faster nowadays than before. Following query select count(*) from information_schema.tables where table_schema = 'public' returns a ...
danilabagroff's user avatar
1 vote
0 answers
167 views

Which order should the columns in a composite key as a foreign key be specified?

Looking at the code from this answer which uses a composite key to control which child tables can contain the parent key, I noticed he specifies the primary key of the foreign table first, then the ...
adam0101's user avatar
  • 163
1 vote
0 answers
166 views

DELETE a single row from a table with CASCADE DELETE picks a slow plan... but not always

Schema Summary A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed. This looks like ...
Dave Horton's user avatar
1 vote
0 answers
822 views

How to properly name a database table

First of all, I understand this question may seem opinion-based, but I know there are some standards for naming conventions and I would like to know which is the best and why. It's not so complex ...
Martin.'s user avatar
  • 111
1 vote
0 answers
2k views

Cannot truncate a table referenced in a foreign key constraint even with no child data?

With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is ...
IMB's user avatar
  • 169
1 vote
0 answers
30 views

Are there any downfalls to not using foreign key relationships in a multi-tenary scheme?

We have a proposed restructure of our CRM containing almost 500 databases containing ~25 tables each into a single multidimensional database with one giant fact table linking everything together. I ...
amaster's user avatar
  • 190
1 vote
1 answer
887 views

Copying table subset from one database to another with same schema

I am trying to subset of a table from one table to another users(id, name, referrer_id, country_id) this table have self referential association with refferer_id as foreign key to itself. I ...
Tachyons's user avatar
  • 235
1 vote
0 answers
3k views

how to dump mysql schema without keys and without constraint ? and keep all of them to alter afterwards

i am using ubuntu 16.04 and want to dump large schema with hounded of tables from AWS rds, remove all keys and foreign key , load data and then add back the keys , is there any automatic tool that ...
Omer Anisfeld's user avatar
1 vote
0 answers
130 views

Weird MySQL column name issue

After trying to use the Propel PHP ORM on my database, I noticed that there is an issue with foreign keys. This is a table with foreign keys: CREATE TABLE `appusers` ( `AppID` INT(11) NOT NULL, `...
user265889's user avatar
1 vote
0 answers
36 views

Strategy to manage foreign key from another system/database

There is a system in the company which stores all the consultants working for the company. Our project needs to store the consultants assigned to clients. We use the id of the consultant in our ...
Tathagat's user avatar