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
2answers
51 views

LEFT JOIN not working as intended

I'm using mysql 8.0.21. I'm trying to join two tables such that I can find the index name of foreign keys. This is my query: SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA, KEY_COLUMN_USAGE....
0
votes
2answers
41 views

How to properly and effective rename a vaue (column) of a foreign key in both tables?

I have 2 tables: users and user_statuses. A user status is of type varchar. It's varchar for the purpose of readability and I don't consider using id/int in this case. CREATE TABLE users( --.............
0
votes
0answers
16 views

Once a foreign key is set to Null after deleting the related parent record, can you then amend the foreign key to a new value?

My question is say you ran a daycare and had a table for the children and another for their related school (with the FK in the child table), if the school of some of the children, for some reason, ...
0
votes
0answers
21 views

Leaving out foreign key when creating a postgresql table

I have been using mysql for the past number of years and i have always wondered? Lets say you have several tables and the tables are so interconnected, in an normal schema, you would have foreign keys ...
0
votes
1answer
20 views

Foreign Key Settings

I will start by mentioning that I am very new to databases. I am working in MySQL. Can anyone give me a layman's view of when you would use which settings for the foreign key options so when you would ...
1
vote
1answer
47 views

How to 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 ...
0
votes
3answers
32 views

mySQL foreign key and primary key

Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is: ...
3
votes
3answers
74 views

Postgres foreign key to a foreign key [closed]

Apologies for asking this like an interview question... Say you have 3 tables A, B and C A is huge B references A’s primary key through a foreign key and is small C wants to reference the data in B ...
0
votes
2answers
37 views

What is wrong with this mysql INSERT statement?

I have table: Book: - id:(int) primary key, autoincrement, not null, - title:(varchar) not null, - price: (decimal) not null and Artical: - id:(int) primary key, autoincrement, not null, - title:(...
2
votes
0answers
39 views

Can a FOREIGN KEY be referring to the same table as the “source”?

I was sure that I had done this countless times, but I can't find any previous use of me doing this. But it seems to make perfect sense, so I assume that it must be possible? However, the manual says: ...
0
votes
0answers
12 views

What is the best relationship for a pair of tables that have optional dependencies but aren't really related?

I have two tables inspections and tasks. The inspection is allowed to have a single task but not many. Inspections don't need tasks and really there is no direct relationship. However, I have a link ...
1
vote
1answer
16 views

PostgreSQL Generate create Foreign keys from information_schema or pg_constraint

Recently on a testing infrastructure, to restore multiple tables (via copy command) I dropped all the foreign keys. I have restored this database schema dump into a different database before dropping ...
0
votes
1answer
28 views

practical effect of not validating FKs in Postgres?

I need to add a foreign key definition to a very large table in Postgres. (Let's assume for the moment that I'm positive that every FK value is actually valid.) I know that I can specify NOT VALID in ...
0
votes
0answers
33 views

Adding a new table to a database - are my dependencies off?

So I'm adding some functionality to an existing table for a system I'm working on. It has a few foreign key relationships and it follows the snowflake design, please see below. I've censored out the ...
0
votes
0answers
19 views

Adding a disabled foreign key to InnoDB table

The concrete problem is having a vast database which is lacking foreign keys, which makes it difficult for developers to work on. The plan was to add disabled foreign keys to it initially, which would ...
0
votes
1answer
22 views

foreign key on multiple tables based on multiple columns

I have a question about design part of my db I have 3 tables like these: CREATE TABLTE projects ( uuid uuid name text ) CREATE TABLTE invoices ( uuid uuid cost text ) CREATE TABLTE ...
0
votes
1answer
26 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
0answers
29 views

AutoFK index disabled during foreign key creation in pgadmin

Trying to create a foreign key, but doesn't allow me to save the changes as it shows the error "Please specify covering index name." Cant specify the covering index unless i toggle the auto fk index ...
1
vote
2answers
34 views

How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint' [closed]

CREATE TABLE EMPLOYEE ( employeeNumber VARCHAR (15) NOT NULL, employeeName VARCHAR (20) NOT NULL, department VARCHAR (10) NOT NULL, phone INT NOT NULL, PRIMARY KEY (employeeName, department) ...
1
vote
1answer
38 views

Foreign keys to primary tables or nested table

In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the ...
0
votes
0answers
34 views

SQL Azure Indexes need Rebuilding for Foreign Keys to work

We have a product that runs off SQL databases. Until recently, each of our clients had their own fileserver running SQL Server, however we have now started hosting on Azure for some existing and some ...
0
votes
0answers
48 views

Create column referencing multiple PK's from same table

I am trying to create an table, menu items, to use in my front end. Menu items can have 1 or many submenu items. So one column must be able to contain 1 or more ID's from other menu item records. ...
0
votes
0answers
11 views

Is duplicating “short cut” foreign keys a good idea to avoid multiple joins?

I have 3 tables A, B and C. C belongs to B, and B belongs to A. 1 entity of A can have 0 or various entities of B, 1 entity of B can have 0 or various entities of C. 1 entity of C has 1 entity of B, ...
1
vote
1answer
53 views

How to filter multiple many to many relationship based on multiple tags?

I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like: Problem table: ----+---------------------------...
1
vote
0answers
17 views

SQLite fails to delete the foreign keys with the cascade delete [duplicate]

I have a simple SQLite database with a foreign key on a master table. This table is a relational table to help mapping Table A to Table B. CREATE TABLE IF NOT EXISTS "PERSON" ( "ID" INTEGER NOT ...
3
votes
2answers
63 views

Referential Integrity - Indirect Foreign Key In “Depth”

Question What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship ...
0
votes
2answers
16 views

mysql - insert multiple values sharing same foreign key

I have the following tables: create table countries ( country_id tinyint unsigned, iso_alpha3_code varchar(4) ); create table provinces ( province_id bigint unsigned, province_name ...
0
votes
1answer
14 views

How to get latest timestamped result from time series in a join table [duplicate]

I have 3 tables node ---- id ip metric --- id key (str) node_id (FK on node) his_metric ---- id metric_id (FK on metric) value (float) datetime (datetime) Question How to write a query that will ...
0
votes
1answer
28 views

Do I need to redesign my Database and get rid of foreign keys for successful replication?

I plan to set up MariaDB replication either in master-master or master-slave mode. The main issue I have now are worries after reading this documentation. We use foreign keys a LOT and we also use a ...
0
votes
1answer
25 views

How can I use the same primary key across multiple tables as a foreign key? [closed]

I am working on a school project. I have a table of employees with a given employee ID. The employee ID on this table is the primary key. I have already set the employee ID as a foreign key on another ...
0
votes
1answer
19 views

PostgreSQL - How to list all foreign key link to Primary key of a table

I have a table res_users with primary key id I would like to list all the foreign key link to id from all the tables in my database.
0
votes
1answer
28 views

Delete/Modify row in one table based on a condition

I have a table structure like create table EMPLOYE ( CodeEmploye varchar2(100) not null, NAS varchar2(100), CONSTRAINT employe_pk primary key (CodeEmploye) ); create table SALAIRE ( ...
6
votes
1answer
165 views

NULL in foreign key column(s)

I have been learning about SIMPLE and FULL matching foreign keys in PostgreSQL and I am wondering if the following thought process is correct: Once at least one of the referencing columns of the ...
1
vote
0answers
31 views

Which order should the columns in a composite key as a foreign key be specified?

Looking at the code from this answer which uses a composite key to control which child tables can contain the parent key, I noticed he specifies the primary key of the foreign table first, then the ...
0
votes
2answers
44 views

mysql - How to remove duplicate rows in parent table and leave one row with the lowest ID, and replace foreign key in child's table with this ID?

I have the situation where I have three tables: hgn_modern_sources +----+--------------+ | id | source | +----+--------------+ | 17 | Something... | +----+--------------+ ...
1
vote
1answer
175 views

Introducing FOREIGN KEY constraint 'FK_X_Y' on table 'X' may cause cycles or multiple cascade paths

So while publishing my database scripts in Visual Studio, I get this exact error: Introducing FOREIGN KEY constraint 'FK_Users_Manager' on table 'Users' may cause cycles or multiple cascade paths. ...
2
votes
3answers
89 views

Foreign key reference to column that is no primary key

I know, foreign keys should uniquely identify rows and therefore reference a primary key. However, consider my example: There are 4 pre-defined categories, and for some combinations of values for ...
0
votes
0answers
9 views

database restore with cross foreign keys on different schemas/databases

As I read on this question Mysql allows cross-schema foreign keys but how to deal with database (schemas) restore? I mean, if table A (schema 1) has a foreign key on table B (schema 2) and table C (...
1
vote
1answer
43 views

Foreign key references on column definitions are ignored. Feature, not a bug — why? [closed]

Good old references constrains. They work like a charm when defined at the table level. create table foo (id int primary key); create table bar (id int, foreign key(id) references foo(id)); insert ...
0
votes
0answers
23 views

How to make foreign key reference to column in parent table of specific table

I am using mysql database, by using it I created five tables course, section, subsection, school and school_course without any problems CREATE TABLE `course` ( id BIGINT AUTO_INCREMENT PRIMARY ...
0
votes
1answer
43 views

Foreign key referencing table with composite primary key when I only need 1 column association

So lets say we have 2 tables on SQL Server database: CREATE TABLE VersionedData ( Id INT NOT NULL, Version INT NOT NULL CONSTRAINT [PK_VerData] PRIMARY KEY (Id, Version) ) And: ...
1
vote
1answer
37 views

Is it possible to enforce corresponding hierarchies?

I constructed the following toy example to illustrate my question. The left side of the schema describes a an abstract product consisting of its parts. The right side of the schema describes actual ...
1
vote
0answers
22 views

DELETE a single row from a table with CASCADE DELETE picks a slow plan… but not always

Schema Summary A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed. This looks like ...
1
vote
0answers
98 views

How to properly name a database table

First of all, I understand this question may seem opinion-based, but I know there are some standards for naming conventions and I would like to know which is the best and why. It's not so complex ...
6
votes
2answers
258 views

Alternatives to storing a record with exactly n multiple foreign keys from the same foreign table, where the relationships can't be repeated

Say there are entities called singulars, and entities called relationships. It takes exactly two singulars to make up a relationships entity. That pair of singulars can't be repeated elsewhere in ...
-1
votes
2answers
45 views

Foreign key twice in same table for different columns?

Server: MariaDB 10.3.21 Client: MariaDB 10.4.12 Given the following structure/data: DROP TABLE IF EXISTS `main`; CREATE TABLE `main` ( `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `...
0
votes
1answer
76 views

Can a single field reference two PRIMARY KEY (or UNIQUE) fields of two different tables? [closed]

These are my tables: CREATE TABLE uregister( u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR(150) NOT NULL, u_mail VARCHAR(150) NOT NULL, u_password VARCHAR(8) NOT NULL, PRIMARY KEY(...
4
votes
1answer
81 views

Why does querying for records that violate a foreign key return incorrect results?

While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant ...
0
votes
1answer
33 views

I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?

The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-...
0
votes
0answers
92 views

What is the difference between candidate key, super key and alternate key

I'm in IT but not in software development or database. I studied about DBMS subject 6-7 years back. I have confusion between these, candidate keys are like ideal candidates to become become primary ...

1
2 3 4 5
17