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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
22 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 ...
kiaderouiche's user avatar
0 votes
1 answer
20 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 ...
Mike Warren's user avatar
2 votes
1 answer
23 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, ...
ifiht's user avatar
  • 123
-3 votes
0 answers
14 views

My Table has the same values for primary Key and foreign key in MYSQL and it is giving me an error of 1062. How do I solve it?

CREATE TABLE DISTRIBUTION_COMPANIES ( ID INT PRIMARY KEY, COMPANY_NAME VARCHAR(30) ); INSERT INTO DISTRIBUTION_COMPANIES(ID, COMPANY_NAME) VALUES('1', 'COLUMBIA PICTURES'); INSERT INTO ...
ms08's user avatar
  • 1
0 votes
1 answer
28 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 ...
ymudyruc's user avatar
1 vote
2 answers
22 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
  • 37
2 votes
2 answers
603 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
54 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
66 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
27 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
26 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
165 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
  • 433
0 votes
1 answer
71 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
49 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
  • 123
0 votes
1 answer
47 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
1k 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
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
0 votes
0 answers
191 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
35 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
62 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
58 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
83 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
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
0 votes
1 answer
62 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
  • 647
0 votes
0 answers
23 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
1 vote
1 answer
3k 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
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
0 votes
2 answers
95 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
851 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
589 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
139 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
421 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
64 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
51 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
115 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
61 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
93 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
402 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
210 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
389 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
51 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
155 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
160 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

1
2 3 4 5
21