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.
1,014 questions
0
votes
1
answer
21
views
How to handle the deletion of records with "shared ownership"?
Consider the following scenario:
In a trading card game, a user may offer to trade their cards. The offer can be public (any user can accept, fk_receiver_id null on insert) or private (only one user ...
1
vote
1
answer
39
views
Issues with Self-Referencing Foreign Key in SQLite
I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table ...
- 111
-1
votes
0
answers
22
views
Rewrite all foreign keys
I have two tables. One has a primary key of text type, and the other references that primary key as a foreign key:
CREATE TABLE public."Guilds" (
guild_id text NOT NULL
)
ALTER TABLE ...
- 99
3
votes
1
answer
212
views
Prevent inserting wrong parent id
I have two tables in a Postgres 17 database: site and page. Each site can have multiple pages and each page can have a parent page:
CREATE TABLE site (
id INT PRIMARY KEY
);
CREATE TABLE page (
id ...
- 175
0
votes
1
answer
57
views
how to write trigger to simulate before update to set foreign key field to null upon violation
I have two databases that synchronize with each other. One database is a full set of data, the other is a limited dataset. This means that the limited database sometimes doesn't have the data the ...
- 11
0
votes
1
answer
26
views
mySQL foreign key error
I'm making a database for a school project in mySQL server and, for some reason, foreign keys won't establish; every iteration of the syntax I attempt gives me an error saying it's wrong, and links to ...
0
votes
0
answers
20
views
Composite FK with 1 column common in both parent and child
I have a parent and child relationship as follows, where Table 1 is the parent and Table 2 is the child. There is only 1 column common in both parent and child tables. For this FK relationship, can I ...
-1
votes
1
answer
51
views
How to INSERT a new record into 2 tables with FKEY inheritance?
Inheritance has been setup with FKEY instead of INHERITS due to FKEY limitation.
My question: How to INSERT a new record into bonds table?
CREATE TABLE IF NOT EXISTS instruments (
id SERIAL,
...
- 1
0
votes
0
answers
25
views
VALIDATE CONSTRAINT of Foreign key pointing to partitioned table for individual partitions instead of everything at once
I have a partitioned table user(id, tenant_id) partitioned on tenant_id that has a hundred partitions (user_1000, user_2000 etc.)
I have a separate existing table events(id, user_id, tenant_id) that I ...
- 269
0
votes
0
answers
25
views
Automatically create VIEWs which expose (using JOINs) a given column of all referenced tables in all referencing tables in a database
I have a PostgreSQL 15 database containing several schemas.
In each schema (representing topics) they are a bunch of tables.
Tables are of two kinds:
child tables, often having up to 10 FK to some ...
- 384
0
votes
1
answer
30
views
How can I find all violations of a proposed foreign key on all columns with a specific name?
Let's say I have multiple tables with a column foobar. And I wish to make to make all these columns reference foobar.id as a FOREIGN KEY. The problem is I know there are violations in this schema from ...
- 64.6k
2
votes
1
answer
166
views
What is an industry standard name for this self-referencing foreign key relationship?
I'm currently in a battle with Entity Framework over trying to make a self-referencing relationship without having to add additional columns/properties. I have confirmed the foreign key I envision is ...
- 153
3
votes
1
answer
400
views
SQL Server not Performing Join Elimination
Given the StackOverflow2010 database, I have created some foreign keys in order to explore join elimination:
Foreign Key 1 - All comments must have a post
Delete comments without a post
DELETE c
FROM ...
- 2,083
0
votes
0
answers
101
views
What is the most performant way of modelling a many-to-many-manies relationship?
I have a data model that looks like this:
CREATE TABLE foo (
id PRIMARY KEY
);
CREATE TABLE bar (
id PRIMARY KEY
);
CREATE TABLE baz (
id PRIMARY KEY
);
CREATE TABLE users (
id PRIMARY KEY
);
...
- 101
0
votes
1
answer
79
views
Adding FK to a newly attached partition slow
We have a partitioned table from which we attach and detach partitions for archiving purposes. When attempting to add a foreign key on the partitioned table, the process is taking a long time (up to ...
- 23
0
votes
1
answer
48
views
Create table that has a sequence column counting from 1 for every different foreign key
Is there a way to define a table that has a sequence column counting from 1 for every different corresponding foreign key column?
I want a "local" serial for every foreign key - sorry for ...
- 121
0
votes
1
answer
41
views
primary key referened by only single foreign key of another table among multiple tables that can reference it
Let's say I have table Account that contains username and password of users to login:-
account (
account_id BIGINT PRIMARY KEY,
username VARCHAR(30),
password VARCHAR(30)
)
-- I have two ...
- 103
0
votes
1
answer
32
views
Create composed key (YEAR+COUNT) field with trigger after insert in SQLITE
I have a table Product with two columns: name of product and price
CREATE TABLE IF NOT EXISTS YearCounters (
year INTEGER PRIMARY KEY,
counter INTEGER DEFAULT 0
);
-- Product Table
CREATE ...
0
votes
1
answer
27
views
A foreign key that references a table and another to reference the parent table (what's the alternative)
Background
I am OK-ish at Database Design...and I am building out this layer first. It is for a KPI tracker app, for real estate investment businesses.
The app's business requirements
This KPI tracker ...
- 101
2
votes
1
answer
92
views
Foreign Key Error in SQLite3 CREATE TABLE Command
Looking for fresh eyes on this, I'm running the command in sqlite3 interactive and can't see the problem. Here's the code:
CREATE TABLE Personas(
PersonID INTEGER NOT NULL PRIMARY KEY,
...
- 123
0
votes
1
answer
47
views
Postgres: automatic action (increment count of foreign key) after update
How to automate an update in a table, after a change in another table ?
Say (please see below) I have a table countries, and a table cities. How to make that, when adding a city in table cities, the ...
- 3
1
vote
2
answers
43
views
one to one relationship on tables
I am creating 2 tables like below, I want these two tables to have one to one relationship and whenever I insert a row to User I want the same PK to be inserted in the other table as well. Would this ...
- 37
2
votes
2
answers
678
views
Design: What should be my PK, FK etc
I'm new to PostgreSQL and SQL in general and I've got a bit of an issue understanding PKs, FKs an serial id and when to use them. I'll try to add info on what I have already tried so far, but I can't ...
- 23
1
vote
2
answers
61
views
MariaDB Foreign Key Pair that references System-Versioned Table Record
I'm altering an open-source booking system to match my employer's needs. I'd like to make it so that editing or deleting a service does not necessarily modify or delete the service that is referenced ...
- 11
0
votes
3
answers
260
views
How to change insert values from 0 to NULL for a specific field in order to avoid foreign key violation?
I have a foreign key linking INSTRUMENT.VENDOR to VENDOR.NAME.
Unfortunately the program where users enter information is attempting to insert into INSTRUMENT with a value of 0 for vendor. I need to ...
- 113
0
votes
0
answers
60
views
Design question: mapping table with allowed combinations
For a platform measuring different metrics we plan a (Postgresql-)database that lets users add sensordevices to different entities. There sensordevices consist of different sensors. To limit the ...
1
vote
1
answer
77
views
What foreign key should I store from users table or clients table?
Let's say I have a table called users that contains basic user information like email and password and I also have a table named clients.
A client is a type of user, so that it will have a field that ...
- 113
1
vote
1
answer
59
views
How do you only get data from the table with the foreign key using SQLite?
I want to be able to get data from the table that owns the foreign key as a primary key, only using data in the WHERE clause from the other table.
I have tried an inner join and I am getting weird ...
- 115
0
votes
1
answer
34
views
How do I write sensible FOREIGN KEY CONSTRAINTS if the PRIMARY KEYS from two tables can reference multiple of the other table's PK
I am setting up a database for a university and I'm struggling to setup sensible FOREIGN KEY CONSTRAINTS (and even tables) because I have two tables in which the PRIMARY KEYS of each can relate to ...
3
votes
3
answers
269
views
How can I know how many tables my DELETE has changed?
Suppose that I have many tables linked by a chain of ON DELETE CASCADE foreign keys. To the best of my knowledge and experience, deleting from one table gives me no notice at all that I have in fact ...
- 823
0
votes
1
answer
86
views
SQL Server using table's Primary Key as part of Foreign Key
The situation is I have a Tenant table, and a tenant based status lookup table. Each with their own autonumber Primary keys.
Now I want to add a column to the Tenant table for a default Status that ...
- 15
-2
votes
1
answer
41
views
What is the correct sentence to express the relationship between the user and his direct manager:
What is the correct sentence to express the relationship between the user and his direct manager:
A many-to-many table must be maintained because managers have several employees under them.
A field ...
- 99
0
votes
0
answers
100
views
How can I make a foreign-key case-insensitive in Postgresql?
I have a Mariab-database using collation utf8bm4_general_ci and I am migrating to Postgresql.
Some tables are foreign keyed using case-insensitive strings (e-mails sourced from different systems).
...
- 123
0
votes
1
answer
49
views
How I can place further constraints upon foreign key?
In sql server I have the following table:
CREATE TABLE mydb.dbo.files (
id bigint IDENTITY(1,1) NOT NULL,
name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
folder bigint ...
0
votes
1
answer
35
views
Existing tables adding composite foreign key constraints
I've got the above set of tables shown in the ER Diagram above.
members have many users.
members have many approval_groups.
approval_groups have many approval_group_users.
approval_group_users....
- 179
2
votes
0
answers
25
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....
2
votes
2
answers
4k
views
CASCADE DELETE per statement
Can I execute something like this in Postgresql, that is, execute CASCADE DELETE for just one statement?
DELETE CASCADE FROM foo WHERE bar = 'baz';
My goal is to delete not just the selected record(s)...
- 105
3
votes
0
answers
171
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`),
...
- 31
0
votes
0
answers
296
views
Best way to bulk create with foreign key
This might have been asked but I have not found a sufficient satisfactory answer yet.
Use case is that users can create a (temporary) network on a map via clicks and then press save to finish creating....
1
vote
1
answer
66
views
building this not-so-necessary relationship is a good approach?
I created a database for an app, where I have several networks, which have several nodes and elements and also several scenarios, all related to the networks. [see picture below]
My question is: since ...
- 113
0
votes
0
answers
77
views
Generated column in a view counting related rows
I have 2 related models, model_A and model_B. There's a 1:n relationship among them, one row in model_A can have several related rows in model_B.
model_A is a view, created with CREATE OR REPLACE VIEW ...
- 117
0
votes
0
answers
191
views
one to one relationship in SQL Modeler
USING ORACLE SQL MODELER
I am trying to make Relationship between 2 entities (Roles and Users). The Users table has role_id as a foreign key. The Relationship between Roles(1) and Users(Many) is 1:N, ...
2
votes
1
answer
86
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 ...
- 121
0
votes
1
answer
102
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 ...
- 658
0
votes
0
answers
56
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
1
vote
1
answer
7k
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
114
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
0
votes
2
answers
180
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
2k
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
893
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 ...