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
1answer
19 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
15 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 ...
0
votes
0answers
24 views

Cannot truncate a table referenced in a foreign key constraint even with no child data?

With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is ...
-1
votes
1answer
20 views

Is there a usecase to create separate indices on the foreign keys of junction/associative table?

Imagine an associative/junction table junction_table_ with (pseudocode) columns like: id_of_table_x REFERENCES TABLE X id_of_table_y REFERENCES TABLE Y PRIMARY KEY (id_of_table_x, id_of_table_y) So ...
0
votes
2answers
76 views

What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?

I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves)....
0
votes
1answer
22 views

Is there a difference between making a composite foreign key for all columns or a foreign key for each column?

In general, is there a difference between making a composite foreign key for all columns or a foreign key for each column? First approach FOREIGN KEY(OrderId, CompanyId) REFERENCES ParentTable(...
0
votes
1answer
23 views

Many to Many association table PK as FK in other table

I have the following tables: school, teacher, school_teacher and student. school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher. A student ...
0
votes
2answers
30 views

Force create foreign key even if reference does not exist

I have two tables foo and bar, bar has column a that references foo.id as a foreign key. When creating table bar before foo, the error column "a" referenced in foreign key constraint does not exist is ...
0
votes
0answers
21 views

Adding a column and new foreign key constraint in one line

Let's say we have a table called Salesman with the folling properties Create Table SalesMan( EmplyeeId int Primary Key, Name varchar(20) Not Null, LastName Varchar(20) Not Null, Email Varchar(...
0
votes
1answer
21 views

Constraints between tables on a variable

im aiming to add a constraint on the number of bicycles allowed to be stored in each terminal - the number differing between terminals. These are my original tables create table terminal( ...
-1
votes
1answer
31 views

When creating a FOREIGN KEY constraint, get “ERROR: column ”xyz“ referenced in foreign key constraint does not exist” [closed]

I keep getting this error in my psql database; bikefacility=# ERROR: syntax error at or near "c" bikefacility-# LINE 1: c bikefacility-# ^ bikefacility-# bikefacility=# ALTER TABLE bicycle ...
-1
votes
1answer
30 views

Mysql - How to delete a row with foreign key referencing multiple rows

I have a table F: CREATE TABLE IF NOT EXISTS `mydb`.`table_f` ( `id_f` INT NOT NULL AUTO_INCREMENT, `fk_1` INT(11) NOT NULL, `fk_2` INT(11) NOT NULL, `column_F` VARCHAR(45) NOT NULL, ...
0
votes
1answer
70 views

Postgres: foreign key constraint for components of a composite type?

In Postgres, is it possible to set up a foreign key contraint involving components of a composite type, in a situation like the following one? Suppose I have CREATE TABLE main ( -- a and b are ...
3
votes
2answers
233 views

ENUM and ID as a Foreign Key

Using PostgreSQL 11. I've tried digging through both Stack Overflow and here and was unable to find an answer on best practices. I'm working on a database design and have arrived at a schema that ...
0
votes
1answer
27 views

Fallback to known existing records when a record is missing

Related to this question, I want to return module contents for various regions/languages. I built my site in English so I know I have all of those pieces, but since I'm getting translations from an ...
0
votes
2answers
41 views

What's an alternative solution to using a null value for a foreign key field?

I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have ...
0
votes
1answer
12 views

Is there any practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window?

I'm very new to Access, so I'd appreciate patience if this is a basic topic. I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography ...
-1
votes
1answer
41 views

In which system table does Postgres store FK cardinality?

I'm trying to reverse engineer a set of PG databases into a Data Model. I've found everything I need to define all the sub-models, tables, columns and FK constraints, but can't find anywhere that ...
0
votes
0answers
21 views

generating a log for Cascade deletes

I have a database which supports a .Net application and i have several parent tables and child tables database design where several tables are linked using FK and there is cascade delete enabled. So ...
0
votes
0answers
40 views

What's the best way to model similar tables with overlapping functionality?

I'm building a sports event/tournament/league management app for myself, and I'm stuck on the best way to model how Teams should relate to the different competition types. Some background regarding ...
4
votes
2answers
166 views

When the primary key is also the foreign key, in Postgres

In Postgres 12, I am tracking photographs. Each is large, about 6 to 8 megs each. For quick browsing I will store a thumbnail image along with the full image. Both full and thumbnail are BYTEA type ...
0
votes
0answers
21 views

Archiving Huge table with Foreign Key dependencies

We have a huge table of ~ 1TB (data+Indexes) which has ~7 child tables with Foreign key relation ship We have decided to archive the huge table but not the child tables(since they are not that big ) ...
3
votes
2answers
72 views

How to make ON CONFLICT work for compound foreign key columns?

I'm having trouble with ON CONFLICT not working for foreign key columns where the foreign key is compound. Here's an example. create table foreign_table ( id_a text not null, id_b text ...
0
votes
1answer
31 views

Postgres: Can I associate index with FK constraint?

Postgres FK constraint and index are independent. Recently I was looked for unused indexes and found trick to eliminate indexes needed for constraint enforcing by: WHERE NOT EXISTS (SELECT 1 ...
1
vote
0answers
21 views

Are there any downfalls to not using foreign key relationships in a multi-tenary scheme?

We have a proposed restructure of our CRM containing almost 500 databases containing ~25 tables each into a single multidimensional database with one giant fact table linking everything together. I ...
1
vote
0answers
96 views

Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer ...
0
votes
1answer
55 views

SQL Server Chaining Many-To-Many tables

I have a scenario where we want to build an inventory description system and I am trying to design the schema but need a little guidance to make sure i'm not veering off the track. The basic premise ...
5
votes
2answers
320 views

Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it: -- ...
0
votes
1answer
105 views

MySQL Workbench doesn't show relationships between tables when using Reverse Engineering(Mac OS)

After running this and successfully creating the database, tables and both the primary and foreign keys, when I reverse engineer my database it won't show the relationships between tables. I'm using a ...
3
votes
2answers
249 views

Migrate foreign key type from char to binary - ways to deal with the fallout?

I am in the process of slimming down a database that has been using char/varchar fields where binary/varbinary would be a better choice (given the data represented is byte arrays). One of the fields ...
0
votes
1answer
39 views

Can I fix this insertion anomaly without using a composite key?

Suppose I have a schema with the following basic elements: Client : id (PK) Pet : id (PK) owner_id (FK to Client) Contract : id (PK) client_id (FK to Client) PetInContract : ...
0
votes
2answers
34 views

Solutions for Introducing a new Entity In Between An Existing Association

I'm looking for best practices in regards to a specific change in schema. I'm using sql. I had the following entities: Project and batches - a project had many batches. The batches table had a ...
1
vote
1answer
55 views

Two FOREIGN KEY constraints with SET NULL targeting the same PRIMARY table

I feel like I'm missing something obvious here... I have a situation with a primary table (let's call it People) and a secondary table (let's call it Groups) with two different roles that people ...
-2
votes
1answer
41 views

ON DELETE / ON UPDATE doubts

I was looking at database past exam from the school that I attend, and I have a question about ON DELETE and ON UPDATE. The question in the past exam was: Show the contents of the tables after ...
0
votes
1answer
28 views

Centralized Database for user information

Our current setup is that we have what I would call a centralized database that holds user information that is shared by several different databases. We have the several databases to separate out data ...
-1
votes
1answer
468 views

errno: 150 “Foreign key constraint is incorrectly formed” [closed]

CREATE TABLE IF NOT EXISTS `Register_Per` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `DOB` varchar(20) NOT NULL, `Gender` CHARACTER(8) NOT NULL, `ConNo` int(11) NOT NULL ...
0
votes
1answer
20 views

how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?

The query I am using to retrieve data is as "select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb," + " sum(totalqt) as tqdb," + " sum(payed) as tpdb," + " sum(...
1
vote
1answer
49 views

Foreign Keys delete data [closed]

I am failing to understand a few concepts about FKs, i have some really big tables (dozens of million rows) with lots of FKs and i want to delete old data, i can disable FKs -- delete Data -- enable ...
-1
votes
1answer
21 views

Is it possible for a foreign key referencing two different columns for different tables in mysql

I have projects table for example: Id | name | description | created_at | updated_at and tasks table like Id | project_id | description | created_at | updated_at I have an comments table Id | ...
1
vote
1answer
53 views

How do I reference a row which has two foreign keys as its primary key?

I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled. As every team combination can only exist once, I ...
-1
votes
2answers
86 views

Does this have a name?

Does this have a name? I don't really know how to describe the situation, and furthermore, is this a design flaw? T4 has a FK to T3, and to T1, but I can get to T1 by T3. The T1, T2 refers to MUST ...
1
vote
2answers
74 views

join vs virtual table in query performance

Suppose I have two tables linked by a foreign-key example DDL's CREATE TABLE raw_collection ( info_datetime DATETIME NOT NULL, asset_id INTEGER NOT NULL, price FLOAT, PRIMARY KEY (...
2
votes
2answers
125 views

How to Exclude Foreign Keys during Publish action in sqlpackage

I am trying to publish a .dacpac extract to a target database, I want to exclude just the foreign key constraints in all the tables. After going through the ExcludeObjectTypes options I do not find a ...
0
votes
1answer
98 views

Changing Column Data Type with FKs

I want to change my column data type from "Datetime" to "Datetime2(7)". If I try to do it with the GUI SQL Server 2014 asks to save all FKs table dependencies and then I get a lock timeout because ...
0
votes
0answers
74 views

Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns

We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints. We decided on the archival since the ...
1
vote
2answers
74 views

Simulate DELETE CASCADE

I need to delete many rows from one table persons, that is referenced by many other tables as FK. If I had DELETE CASCADE, I could just delete the records from persons, and automatically delete from ...
1
vote
1answer
23 views

two-table-over <+> functional relationship: maintaining referential integrity

I have three tables, the last two having a many to one relationship with the first, written here in SQL: create table study ( id bigint primary key, name text ); create table treatment ( ...
9
votes
2answers
137 views

Change referenced index for a Foreign Key

I have something like this: CREATE TABLE T1 ( Id INT ... ,Constraint [PK_T1] PRIMARY KEY CLUSTERED [Id] ) CREATE TABLE T2 ( .... ,T1_Id INT NOT NULL ,CONSTRAINT [FK_T2_T1] ...
0
votes
2answers
57 views

Database design - passenger, driver, wallet. How to handle multiple foreign keys with Id and type fields? [duplicate]

I have to design a database schema for new project. I will use PostgreSQL for the implementation of the schema. Right now I'm stuck in a scenario where I need to handle foreign keys based on type and ...
5
votes
1answer
133 views

Why should foreign keys be implemented in my relational database? [duplicate]

Earlier today, I sparked a rather unexpected debate with my professional peers on the topic of foreign keys in our company's databases. In a nutshell, a software engineer who is senior to me stated ...

1 2 3 4 5 16