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
3 votes
1 answer
701 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not all)....
1 vote
1 answer
2k views

Optimizing single-row DELETEs from a table with many FOREIGN KEY references

In a database I am working on, there is a table called persons with about one million rows, and 60 FK (FK) constraints from other tables (some with many millions rows) pointing to it. If I delete one ...
14 votes
1 answer
26k views

Changing foreign key to ON DELETE CASCADE with least amount of impact

I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction: begin; alter table posts drop ...
1 vote
1 answer
886 views

Copying table subset from one database to another with same schema

I am trying to subset of a table from one table to another users(id, name, referrer_id, country_id) this table have self referential association with refferer_id as foreign key to itself. I ...
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 ...
3 votes
1 answer
348 views

Table was deleted during ALTER, how to recover?

I tried to ALTER a table to add a FOREIGN KEY as: ALTER TABLE artists ADD FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE; and I received an error of ERROR 2013 (HY000): ...
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, ...
2 votes
2 answers
1k views

How to delete foreign key with lots of references

Table B have several thousand records that references table A with a foreign key. The application sends a request to delete the row from table A. It's important that the synchronous operation would ...
3 votes
3 answers
23k views

Is it necessary for a foreign key to be a primary key in another table? Why?

I am new to database implementation. I want to know if it is necessary for a foreign key to be primary key of another table. Foreign key is not unique - it may have duplicate entries. If it must be ...
1 vote
1 answer
467 views

Thousands foreign keys referenced to one table and other postgres scaling philosophy

I'm inspecting one legacy and quite big database which is growing faster nowadays than before. Following query select count(*) from information_schema.tables where table_schema = 'public' returns a ...
-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 ...
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 ...
0 votes
1 answer
436 views

Audit table and foreign key

I have a database with multiples tables that must be audited. As an example, I have a table of objects defined with a unique ID, a name, and a description. The name will always be the same. It is not ...
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 ...
1 vote
1 answer
831 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'...
0 votes
1 answer
876 views

MySQL: Foreign key constraint on composite key with the referenced table a function of a constituent column of the key

I have two MySQL tables customFieldsText and customFieldsMultipleChoice. Both tables have an auto-incremental unsigned int primary key. I'd like to create a new table called customFields with the ...
0 votes
2 answers
359 views

Using secondary id as foreign key to other table

So I have a Customer table that is linked to different tables. For instance, a Configuration table is linked to Customer table. Instead of using Customer Id as foreign key to the Configuration table, ...
1 vote
1 answer
16k views

How do I represent foreign key in an Extended ER Model in Chen notation?

This is a spin-off of the question at How to represent foreign key in an ER diagram? which is too vague about the needed notation. There the idea of underlining FKs with a dotted line is stated. I ...
2 votes
2 answers
602 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 ...
0 votes
1 answer
4k views

Insert data into multiple tables connected by foreign key PostgreSQL

I have two tables which are linked by a foreign key. CREATE TABLE clients ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name character varying, phone character varying, ...
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 ...
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 ...
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 ...
1 vote
1 answer
381 views

Ensure same tenant in a Many to Many relationship

I am trying to draft a very simple data model for a multi-tenant database. So far I have only 3 tables: company: pretty much my tenant account: is part of one company and zero or more groups ...
0 votes
1 answer
2k views

mysql how to copy foreign key data from one table to another

I have 3 tables. Each table has its own primary key "id" table 2 has a foreign key that references table 1's primary key table 3 has a foreign key that references table 2's primary key. I ...
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:...
1 vote
1 answer
1k views

ORA-02270: Error report: no matching unique or primary key for this column list

I have created two tables: CREATE TABLE team ( teamname VARCHAR2(30) NOT NULL, carndate DATE NOT NULL, teamnomembers NUMBER(2) NOT NULL, charname VARCHAR2(30), ...
2 votes
2 answers
5k views

size of a primary key and foreign key

Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ? Thank you for help.
0 votes
1 answer
2k views

Why does MySQL allow duplicate index names, but not duplicate foreign key names?

I can of course accommodate the requirement that foreign key names must be unique across all tables, but I've always wondered why the same requirement doesn't apply to index names? Is there a good ...
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 ...
1 vote
2 answers
4k views

SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails

I am trying to install Zabbix by following this guide. I ran into the following problems when trying to run data.sql after running scheme.sql successfully mysql -D zabbix -uzabbix -pPassword < /...
13 votes
2 answers
90k views

Cannot drop column : needed in a foreign key constraint

I have a table with two foreign key constraints as below: mysql> SHOW CREATE TABLE `user`; CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `region_id` int(11) unsigned ...
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 ...
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 ...
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 ...
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 ...
4 votes
1 answer
4k views

Update all foreign keys to a different primary key

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. Let'...
-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 ...
0 votes
0 answers
48 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). ...
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 ...
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....
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)...
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....
3 votes
0 answers
98 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`), ...
0 votes
0 answers
188 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....
2 votes
5 answers
10k views

Disadvantages to using ON DELETE CASCADE on every foreign key?

Our application currently has "soft delete" built-in to most tables with a BIT "deleted" column. There is now a requirement for "hard delete" functionality. Are there any ...
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 ...
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 ...
2 votes
1 answer
987 views

How to link a customer to a country when the city/state isn't known

We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the ...
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 ...

1
2 3 4 5
20