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.
975
questions
0
votes
1
answer
35
views
Better way to return list of FK "parent" tables?
Here's what I currently do in Postgres 9.6. (I know it's EOL; that's not my choice to make.)
I don't want the definitions, FK names, or any of the other clutter you get with \d <table>. Just ...
- 606
0
votes
0
answers
13
views
How MySQL detects that an index is automatically generated?
MySQL creates an index for any foreign key column. Detecting those automatically created indexes is important for unattended migrations on servers.
For example if I manually create a new index on the ...
- 101
0
votes
1
answer
29
views
How to disable FOREIGN KEY referential integrity trigger in PostgreSQL without disabling other triggers in table?
FOREIGN KEYs referential integrity in PostgreSQL can be disabled by executing:
ALTER TABLE schema.my_table
DISABLE TRIGGER ALL;
But unfortunately the command will also disable all other triggers ...
1
vote
0
answers
44
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 ...
- 111
-1
votes
0
answers
35
views
Relational DB engine with support for column as a list of foreign keys
Is there some sort of DB engine that is relational (like MySQL's InnoDB/PostgreSQL)
and supports a type of column that is basically a list of foreign keys?
Is something like that in some standard and/...
- 99
0
votes
2
answers
39
views
Why should I manually name foreign key constraints?
I see people talking about the best way to name foreign key constraints. I have never manually named them, I just get whatever my RDBMS gives it, like tableName_ibfk_1. If I've never seen a need to ...
- 143
1
vote
1
answer
96
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'...
- 11
0
votes
1
answer
37
views
Delete parent without deleting children
I have three table: Products, Stocked products, sold products.
Products and 'stocked products' are different tables because there are different batches of the same product stocked with different ...
0
votes
1
answer
43
views
Foreign Key Referencing ID of One of 2+ Other Tables
Given 3 tables — e.g. posts, comments, and tags — is there a way of having a foreign key in one of them referencing a primary key of one of the other two?
CREATE TABLE IF NOT EXISTS posts (
id ...
- 121
1
vote
1
answer
68
views
Postgres: does marking a Foreign Key constraint NOT VALID affect query planning?
One can use ALTER TABLE ADD FOREIGN KEY ... NOT VALID to skip checking the FK constraint on existing rows at creation time (and only have subsequently inserted/updated rows checked).
Aside from the ...
- 43
0
votes
2
answers
25
views
Can a foreign key refer to a subset of parent table's rows? (Value domain constraint)
Our project massively uses so-called (enumerated) data value domains, or (coded) value domains, as defined by the Australian Institute of Health and Welfare, and in The Data Administration Newsletter. ...
0
votes
1
answer
25
views
Why am I expected to use parentheses notation instead of regular dot notation when defining foreign key relationships?
Here's basic SQL code
create table users (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(50),
last_name varchar(50),
age tinyint
);
insert into users (name, last_name, age)
values
(&...
- 3
0
votes
0
answers
18
views
Can I display more than one column as foreign value description in phpMyAdmin?
phpMyAdmin allows me to select a different column to display foreign values, so instead of having to look for the key, I can look for something more understable from human perspective like a city name:...
- 1
1
vote
0
answers
28
views
Modify parent's and child's foreign key value of a record
Consider following relationship on MySQL database.
+--------------+ +---------------+
| Child | | Parent |
+--------------+ 1 +--------...
- 111
0
votes
0
answers
36
views
Is this an abuse of UniqueIdentifiers?
I'm designing a new Sql Server database. Every major table uses a UniqueIdentifier as a primary key. I'm adding in a new data object called "Notebook" which is a universal object that can ...
- 101
-1
votes
1
answer
39
views
How to Link newly inserted row with Foreign Key?
In my database I have 2 tables linked together with a foreign key relationship.
Parent Table = controls.slot_table
Child Table = controls.IO_table
When a new row is inserted into the parent table (A), ...
- 3
3
votes
1
answer
393
views
Are individual indexes necessary on columns that are included in a compound primary key?
I have this M2M join table:
CREATE TABLE [dbo].[RecipientsDonors]
(
[RecipientId] [int] NOT NULL,
[DonorId] [int] NOT NULL,
CONSTRAINT [PK_RecipientsDonors] PRIMARY KEY CLUSTERED
(
[...
- 549
0
votes
1
answer
34
views
Join two tables with similar columns
I hva two tables as is:
Table univers:
client
commercial
Axel
Tony
Mike
Jhon
Chris
Jhon
Table transactions
frclient
toclient
commission
Axel
NA
10
Mike
NA
8
NA
Mike
15
Axel
NA
4
NA
Chris
5
...
- 33
0
votes
1
answer
79
views
"ON UPDATE" in self referencing table
I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too.
At ...
- 23
2
votes
1
answer
74
views
What's a good way to handle cross-database keys in PostgreSQL?
I'm writing an accounting app that uses MySQL, but I'm thinking about switching to PostgreSQL. My app has a meta database that stores users, document types, a list of companies, etc. Each company (a ...
- 143
0
votes
0
answers
27
views
How do I automatically insert a row into parent table when a row is created in a child table?
I am working on a MySQL database that has to treat organizations and people as interchangeable in some instances and not others. Because of this, I created a table called LegalEntity as a superclass. ...
0
votes
1
answer
69
views
Cant perform partitioning after Foreign Keys were dropped
I'm trying to partitionate a table that has almost 1TB in size in MySQL, I already dropped al my FK using this command for all the FK:
ALTER TABLE mytable DROP FOREIGN KEY my_fk;
but when I try to ...
0
votes
2
answers
49
views
Script disabling/enabling foreign keys
I want to script disabling and enabling Foriegn KEY constraints.
I'm testing with the following setup. As you can see from below I am able to successfully disable the constraints but I can't seem to ...
- 105
0
votes
0
answers
68
views
Foreign key constraints fails even if I disable FOREIGN_KEY_CHECK in MySQL
I'm working with MySQL v8.0.16.
I have a binlog file from MySQL server A that I want to load in my MySQL server B. Server B has the same schemas as A, but not the data. When I attempt to load the ...
1
vote
1
answer
62
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
...
- 11
0
votes
1
answer
129
views
How to drop a table referincing a very trafficated table easily?
Let's say I have this two tables
CREATE TABLE users (
id_user serial primary key,
name text NOT NULL
);
CREATE TABLE useless_table (
id serial primary key,
id_user int4 NOT NULL,
...
- 284
7
votes
1
answer
798
views
There is no index in the referenced table where the referenced columns appear as the first column
I have two EXISTING mysql/mariadb tables such as:
CREATE TABLE `ice_product` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`long_description` text DEFAULT NULL,
`long_summary_descr` text DEFAULT ...
- 181
1
vote
2
answers
356
views
Foreign key constraint not showing in sysobjects but cannot be created due to conflict with itself
I have an Microsoft SQL Server 2019 Enterprise Edition database where I had a relationship between two tables: Properties and Photos with Properties being the primary key and Photos being the foreign ...
- 143
1
vote
1
answer
18
views
Key constraints/indexes/etc... confusion on my part
I have two tables, each carry a column labelled "CN" which is a GUID value generated by my application. The first table holds user data, the second table holds logon data (in this case, salt ...
- 33
1
vote
0
answers
180
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 ...
- 11
0
votes
2
answers
162
views
Is it possible to insert cyclic foreign keys that are AUTO INCREMENT primary keys?
I am trying to model a quiz structure, where there are questions and a unlimited number of answers, where only one is correct.
Example:
Question A
Answer A
Answer B
Answer C (Correct Answer)
Answer ...
- 101
1
vote
1
answer
166
views
Cascading foreign key DELETE with tables that have RESTRICT constraints
I have a database that stores recipes for multiple organizations. So every table has an org_id pointing to the orgs table to allow each organization to have their own set of recipes and ingredients.
...
- 113
1
vote
1
answer
206
views
JSON colunm vs Reference key approach - Postgres
Which is a good database schema design by considering the performance and long-term large records?
Database: Postgres
I have two ways to design the table structure:
Use a JSON column
Go with a ...
- 113
-1
votes
1
answer
34
views
What arrow is used to specify the relationship between a junction table and the FK tables [closed]
This is part of my first database design. I am unsure what type of arrow to use to describe the relationship:
The tables are parking space, parking space features, and features.
I am unsure of how to ...
- 1
0
votes
1
answer
1k
views
Inserting multiple values into multiple tables with generated foreign keys
Let's say I have the following schema:
CREATE TABLE foo (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
content text NOT NULL
)
CREATE TABLE bar (
id integer PRIMARY KEY GENERATED ALWAYS ...
- 3
4
votes
1
answer
222
views
Why does SQL Server use INDEX SPOOL when there is already a relevant index during FK check?
I've got two tables in SQL SERVER 2019: Condition table with 2 FK on Reference table. Condition table has appropriate indexes on FK columns. The schema screenshot and SQL script to generate it are ...
- 49
0
votes
1
answer
628
views
How would I use "LEFT JOIN" and "IF NOT EXISTS" in this case?
There're 2 main tables: drivers and driver_riders that are connected by a foreign key. Every day some of the drivers make a single or multiple rides.
There's also a table driver_rewards
driver_rewards
...
- 35
2
votes
2
answers
55
views
How to extract the subquery of an update script into a CTE?
I store groups and their members in two tables where the member table references the group_id as a foreign key:
CREATE TABLE group_table (
group_id INT,
group_name VARCHAR(8),
...
- 127
1
vote
1
answer
788
views
Postgresql: Drop detached partitions
I am trying to implement an automatic archiving system for some partitioned tables in pg14.
The process I am trying to implement is as follow :
I start with several tables partitioned by date with ...
- 21
0
votes
0
answers
96
views
Better way to store stock levels for products?
I currently have 3 tables: locations, products, and stock, which are pretty self-explanatory:
The fields of stock are location_id, product_id, and quantity, which is great... until I add a new ...
- 111
1
vote
1
answer
994
views
How to determine which foreign keys are circular? As highlighted by warning from pg_dump
When carrying out pg_dump on a (timescale) database running within a local container I have the following:
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: ...
- 276
1
vote
1
answer
349
views
Why is my foreign key yet violated while altered and set "DEFERRABLE"?
I have a case where I have deletions occurring in cascade in an integration test.
The tests are performed on a database created from a test data dump with the additional application of the last ...
- 34
0
votes
1
answer
41
views
create table based on another table
I have a table called " Product " it has: ID_P, Name, expiration date
Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, ...
- 41
0
votes
0
answers
119
views
Foreign Keys As Composite Primary key
Can two non-key attributes of the same table be the composite primary key of another table??
In relation R(BCFGH) following set of FDs hold:
B -> CFH
CH -> G
Since CH -> G is transitive ...
0
votes
0
answers
388
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,
"...
- 1
2
votes
1
answer
43
views
Foreign key references a many-to-may table (Reinforces the access right)
I have this scenario in an e-commerce project:
Category table, Store table, and Bid table.
So, it should work like this:
categories are at the top.
stores can have many categories (let's name it ...
- 23
1
vote
1
answer
167
views
Set Default Value of Column Equal to PK of inserted record, and use it as Foreign Key Reference
I'm using MariaDB 10.3, and what I'm trying to do is an establishment of a 1-to-n relationship as for example specified here in the section "One-To-Many-Relationship". I'm trying to do so ...
- 163
1
vote
0
answers
22
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:
...
- 111
3
votes
2
answers
59
views
Any benefit in replacing a varchar for an index in the main table?
I'm 'inherithing' the job of a database admin for a very small team (3-4 people) and while I know how to execute SQL queries relatively well and understand the basics of programming efficiency , I ...
- 133
2
votes
1
answer
300
views
MySQL InnoDB foreign key requirements (index/unique/primary)
MySQL 5.6 Doc has set certain conditions and restrictions on foreign keys:
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must ...
- 143