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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
45 views

There are 2 ERD Designs, and one has a strange combination. I am told both are correct. why?

One of the designs seems clear: A REQUESTEDPRODUCT is picked from STOCK (which has many locations). The primary key is a composite key of 4 foreign keys. The other design is more unclear. The "...
user avatar
0 votes
4 answers
76 views

Foreign key location

This is a question from a Master's degree. What is the correct response to the question depicted in the image, and why? Or do you believe there could be multiple correct answers?
user avatar
0 votes
0 answers
11 views

Should I create a linking table between two tables with existing IDs or add a column to an existing table

So I have two tables. Not with these exact columns but it's just to illustrate the point users id | forename | surname forms id | name Using MS SQL and was wondering what's best practice to link a ...
user avatar
  • 194
0 votes
1 answer
42 views

Foreign key refencing multiple primary keys

I have multiple tables sharing a common unique ID in my Postgres database. Parent tables: table1 - id1 table2 - id2 table3 - id3 id1, id2, and id3 are unique across the database. Child table: ...
user avatar
0 votes
1 answer
40 views

Select a row using a foreign key in another table

Hi I'm using python and sqlalchemy and postgresql for creating a relational database I have 2 tables Users and Connections Users have information about each user with their id as primary-key and ...
user avatar
-2 votes
0 answers
12 views

How to extr on prem to azure with FK intact

Source is on prem sql dB Dest is Azure MI sql dB and need foreign key relationships intact from source Need is to select from on prem by say site code from table1, and then select those in table2 with ...
user avatar
  • 1
-1 votes
2 answers
34 views

How to design to avoid duplicate Foreign keys

I wasn't really sure how to title this question. I have three tables User, Photo and Album. A user has many Photo objects and many Album objects. However, after a User has added a given photo they may ...
user avatar
0 votes
1 answer
25 views

Unique constraint replaces existing foreign key

I have two tables, parent and child, the child table has a FK on the parent table using the parent_id column. When adding this FK I can see that the child table has both the FK and an index to ...
user avatar
  • 151
0 votes
0 answers
10 views

How to use an ms access db to create job postings

I recently went to onetonline.org and downloaded each section of various job descriptions they had posted. Each section is an excel SS. I then edited each slightly to leave the first line as the ...
user avatar
  • 1
0 votes
0 answers
34 views

Are there performance issues with FKs on different schemas?

In order to perform functional decomposition of a "big blob" of a database the first step we wanted to take is to separate different modules of the database into separate schemas on the same ...
user avatar
1 vote
1 answer
27 views

referencing data from another table without using a join

Hi all so I have a homework assignment that I am having difficulty understanding. the problem I have been given is this: ix. Referring to the customers and shipping_details tables on the ER, ...
user avatar
1 vote
0 answers
27 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: ...
user avatar
  • 111
1 vote
1 answer
44 views

Postgres too Many References to Single Table is good or bad design

I have a Postgres database schema for a web app that needs real-time data and hence a huge volume of select, update, insert queries. Data manipulation is done via Postgres stored procedure mostly I ...
user avatar
0 votes
0 answers
8 views

Persist state on derived data structure

I have a graph in my app, that I need to save to a db. So, first thing, I created a Nodes table and Edges table: Node - id PK - name TEXT - x INT - y INT Edge - id PK - from FK REFERENCES Node - to ...
user avatar
  • 101
2 votes
2 answers
85 views

foreign key table update impact

Lets say there is orders table with few foreign keys. One of them is addressId. Orders -- Id -- orderNum -- customerId -- addressId (foreign) CustomerAddress -- Id -- customerId -- street -- cityId --...
user avatar
12 votes
2 answers
1k views

Why does PostgreSQL allow certain type mismatches in Foreign Keys?

While teaching a database basics course, a student asked about Foreign Keys whose data type does not match the data type of the thing (e.g. Primary Key) they are referencing. For example, all numbers ...
user avatar
0 votes
0 answers
22 views

Best approach to handle postgres table that will hold foreign keys of multiple tables

I am looking for the best way to model a table that will contain a foreign key to multiple tables. I have a table called actions that holds text values like Add Update Remove etc. Another table called ...
user avatar
  • 101
3 votes
0 answers
28 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. ...
user avatar
0 votes
0 answers
49 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?
user avatar
0 votes
1 answer
49 views

Combine tables with different foreign key values for same data (firefox)

I am combining my history in firefox from two computers with the following commands: ATTACH 'filename' AS toMerge INSERT or IGNORE INTO moz_origins SELECT * FROM toMerge.moz_origins; INSERT or IGNORE ...
user avatar
1 vote
1 answer
41 views

Can a single parent table have more than one child table?

I am learning databases and sql. I am trying to understand if one single parent table can have multiple child tables, and if yes, just how common is it in financial companies? If this is allwoed, is ...
user avatar
0 votes
1 answer
1k views

ON CONFLICT DO UPDATE command cannot affect row a second time when trying to pass additional columns from a CTE in postgresql

Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple ...
user avatar
0 votes
1 answer
37 views

How do I create a primary composite key from three tables?

Suppose I have tableA with A.id, tableB with B.id and table C with C.id. Now I want to create a table D that has a primary composite key(A.id, B.id, C.id). How can I do this? create tableD( A.id ....
user avatar
0 votes
2 answers
95 views

Massive DROP on a production database with the shortest downtime

I was redesigning legacy database and have merged lots of tables into just four and now I'm looking for a way to drop a several thousand obsolete tables as quickly as it possible. As far as I know we ...
user avatar
0 votes
2 answers
35 views

Oracle parent child move failed

I have two tables, a parent and child table, which have referential integrity declared between two tables and try to move both the tables so I can delete rows. I start with the child table and the ...
user avatar
  • 103
0 votes
1 answer
47 views

When should I use Index on PostgreSQL? [closed]

In my Java (Spring Boot app), I have the following entity definition which has a relationship with Company entity via companyUuid property. I will also let users to search by name field. We do not ...
user avatar
  • 3
0 votes
0 answers
18 views

Create SQL Server foreign key link to multiple tables?

Can I have a constraint check if a column value is in either of two other columns? As an example, if I have a website and the users of the website should be staff or students, can I check the username ...
user avatar
0 votes
3 answers
80 views

How can I define circular referential integrity constraints?

I have two tables mutually referencing each other, and I get this error when trying to insert into one of them: "Foreign key constraint is incorrectly formed". I have been stuck for three ...
user avatar
0 votes
0 answers
193 views

The INSERT statement conflicted with the FOREIGN KEY constraint - although value exists in referenced table

I am trying to insert a row into a table that has a foreign key constraint, with below query: INSERT INTO Table1 ( ID, tNum, CompanyID, -- this column has foreign key on it ...
user avatar
0 votes
2 answers
95 views

MYSQL - Cannot update a parent row: a foreign key constraint fails

db-fiddle link SCHEMA QUERY -- GRAPH INFO TABLE-------------------------------------------------------------- CREATE TABLE GRAPH ( graph_id CHAR(32) NOT NULL PRIMARY KEY, name ...
user avatar
0 votes
0 answers
24 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....
user avatar
  • 1,331
4 votes
1 answer
123 views

Indexed view maintenance with foreign key not elided when inserting parent row

TL;DR; Given an indexed view that has a JOIN between two tables that already have a foreign key relationship along with a WHERE predicate on the parent table. When inserting into the parent table of ...
user avatar
  • 5,194
1 vote
1 answer
56 views

MySql 8.0 Foreign Keys Missing in InnoDB

I have created 10 tables in MySql 8.0 using the InnoDB engine. I defined relationships using the references statement. When I view the tables using show create table, the foreign keys are missing. I ...
user avatar
  • 111
0 votes
1 answer
50 views

Does NOT FOR REPLICATION affect query plan generation?

I do understand why replicated articles have not trusted FK constraints, but wanted to know if I force FK for trusted anyway, will query plan actually change?
user avatar
0 votes
0 answers
21 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 ...
user avatar
  • 109
0 votes
1 answer
602 views

1822, "Failed to add the foreign key constraint. Missing index for constraint in referenced table script [closed]

create table TruckMake( TruckMakeID char(3) NOT NULL, TruckMakeName varchar(20) NOT NULL, primary key (TruckMakeID)); create table TruckModel( TruckMakeID char(3) NOT NULL, TruckModelID char(3) NOT ...
user avatar
3 votes
1 answer
198 views

Can optional foreign key cause trouble in the long term?

In a nutshell, we have two tables: Document 1 (strong entity) Document 2 (weak entity) Document 1 has many Document 2 and Document 2 has only one Document 1. That being said, sometimes we need to ...
user avatar
0 votes
2 answers
37 views

Does anyone know how to make two two-column foreign keys from a single table?

I tried the following, but I am receiving an error message. Table goleo mysql> create table goleo( -> id int, -> nombre varchar(30), -> goles int, -> primary key (nombre,...
user avatar
0 votes
1 answer
34 views

A table that has two foreign keys to the same column in another table?

I need to create a database structure for a 1v1 game. This is what I have designed so far. Now here is the problem: I want to display all the data of matches on the screen, but instead of the foreign ...
user avatar
  • 103
0 votes
0 answers
59 views

Constraints on many-to-many relationship

I have a multi-user task tracker. Users are in table user: +---------------+---------+ |user_id(int,PK)|name(str)| +---------------+---------+ |1 |Alice | |2 |Bob | +-...
user avatar
0 votes
1 answer
38 views

Key column 'BranchNo' doesn't exist

CREATE TABLE Staff (staffNo varchar(10) PRIMARY KEY, FName nvarchar(20), LName nvarchar(20), position varchar(15), ...
user avatar
1 vote
1 answer
30 views

Show all ForeignKeys which point to a particular row

How to list all Foreignkeys (table + column + pk-of-that-row) which point to a particular row in a given table. Example: the table "user" has row with PK '1234'. I want to see all FKs which ...
user avatar
  • 1,331
-1 votes
1 answer
101 views

How to temporarily disable foreign keys in Amazon RDS Aurora PostgreSQL? [duplicate]

Hi I am using DMS to load data from physical server to AWS Aurora PostgreSQL, but I am getting error for foreign key constraint. I want to disable using any sp / function. Could you please help me?
user avatar
2 votes
1 answer
53 views

Are foreign key share locks taken out atomically?

I'm trying to understand how a deadlock happened in our database and I'm wondering if the culprit could have been an update that did not run in a transaction. If I have two tables CREATE TABLE user( ...
user avatar
  • 459
0 votes
1 answer
39 views

Deleting records from two-child tables in parallel (concurrently)

I have a postgres database where I am using the pg_cron extension to automatically perform some database "maintenance" at night time - Specifically, my database has some "offers" ...
user avatar
  • 111
0 votes
0 answers
31 views

Foreign key on another foreign key && Pivot Table

I need to design a web app that function as poll system. It should have multiple polls with only 2 contestants which can be repeated in other polls too. For each contestant in the poll I need to count ...
user avatar
0 votes
0 answers
30 views

GDPR and database data relation

In my website, users can report comments. Those reports are verified by operators and, based on a conclusion, they will mark the report as "No action" or "Deleted". The "...
user avatar
  • 1
1 vote
1 answer
122 views

Can a MySQL FOREIGN KEY CONSTRAINT reference a column value rather than a column name?

I have a child table whose rows are related to various parent tables by parent_table_name, and parent_id columns. Can I set up a foreign key constraint for these relationships? In the tables below, I ...
user avatar
  • 111
0 votes
1 answer
236 views

Oracle: "table is mutating, trigger/function may not see it"

(Yes, this is one of those question again...) I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server. I have a table I1 (called t_link in my ...
user avatar
  • 173
1 vote
2 answers
216 views

How do I get a list of all tables in a mysql db, ordered by foreign key dependencies?

I want a list of tables, ordered by foreign key. So if table "Book" has a FK dependency on table "Author", Author would be listed first and Book second. I have no cyclic dependency ...
user avatar

1
2 3 4 5
19