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
1 vote
2 answers
19 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 ...
umarkaa's user avatar
  • 35
2 votes
2 answers
591 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 ...
JUHJUH's user avatar
  • 23
1 vote
2 answers
40 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 ...
Nick Parrill's user avatar
0 votes
3 answers
51 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 ...
Tyler N's user avatar
  • 113
0 votes
0 answers
56 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 ...
sebastian.do's user avatar
1 vote
1 answer
56 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 ...
Thiago Guimarães's user avatar
1 vote
1 answer
26 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 ...
av4625's user avatar
  • 115
0 votes
1 answer
25 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 ...
heeeresjohnny's user avatar
3 votes
3 answers
158 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 ...
J. Mini's user avatar
  • 139
0 votes
1 answer
68 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 ...
mcsov's user avatar
  • 15
-2 votes
1 answer
39 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 ...
Pedro Gómez's user avatar
0 votes
0 answers
43 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). ...
jared's user avatar
  • 113
0 votes
1 answer
46 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 ...
Dimitrios Desyllas's user avatar
0 votes
1 answer
27 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....
Matthew Lenz's user avatar
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
2 answers
807 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)...
Timur Shtatland's user avatar
3 votes
0 answers
89 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
0 votes
0 answers
171 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....
sebastian.do's user avatar
0 votes
0 answers
33 views

Archiving tables with foreign key constraints in PostgreSQL

I'd like to get your thoughts on the approach to archiving data from a set of tables with foreign key constraints. Please find the tables details below. booking (Table):ID (UUID), created_date_time ...
Vamsi Krishna's user avatar
1 vote
1 answer
61 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 ...
oakca's user avatar
  • 113
0 votes
0 answers
57 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 ...
user1632812's user avatar
0 votes
0 answers
64 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, ...
datascinalyst's user avatar
2 votes
1 answer
73 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
0 votes
1 answer
61 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 ...
RonJohn's user avatar
  • 625
0 votes
0 answers
21 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 ...
Tomáš Fejfar's user avatar
0 votes
1 answer
2k 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 ...
Tiago Stapenhorst's user avatar
1 vote
0 answers
76 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
0 votes
2 answers
78 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 ...
aswine's user avatar
  • 143
1 vote
1 answer
724 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
0 votes
1 answer
473 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 ...
Azzedine Bk's user avatar
0 votes
1 answer
120 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 ...
psygo's user avatar
  • 121
1 vote
1 answer
324 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 ...
colophonemes's user avatar
0 votes
2 answers
58 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. ...
Philippe Cloutier's user avatar
0 votes
1 answer
42 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 (&...
Sergey Zolotarev's user avatar
0 votes
2 answers
90 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:...
xerez's user avatar
  • 1
1 vote
0 answers
57 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
0 votes
0 answers
39 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 ...
SvdSinner's user avatar
  • 101
-1 votes
1 answer
85 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), ...
Hibbert's user avatar
3 votes
1 answer
401 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 ( [...
InteXX's user avatar
  • 559
0 votes
1 answer
39 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 ...
COPEGUEST's user avatar
0 votes
1 answer
188 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 ...
bguernouti's user avatar
2 votes
1 answer
336 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 ...
aswine's user avatar
  • 143
0 votes
0 answers
46 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. ...
SlowlySwift's user avatar
0 votes
1 answer
146 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 ...
Danilo Bassi's user avatar
0 votes
2 answers
141 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 ...
Pugzly's user avatar
  • 105
0 votes
0 answers
231 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 ...
Joseph Pena's user avatar
1 vote
1 answer
135 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
1 answer
221 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, ...
Sotis's user avatar
  • 306
8 votes
1 answer
2k 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 ...
lrkwz's user avatar
  • 191
1 vote
2 answers
726 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 ...
Daniel's user avatar
  • 143

1
2 3 4 5
20