Skip to main content

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
27 votes
3 answers
14k views

How to have a one-to-many relationship with a privileged child?

I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
cubetwo1729's user avatar
100 votes
3 answers
478k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
Stéphane's user avatar
  • 5,605
17 votes
2 answers
19k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
Raiden Freeman's user avatar
32 votes
3 answers
9k views

Is using multiple foreign keys separated by commas wrong, and if so, why?

There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called DealCategories with the following structure: DealCategoryId (...
Sarawut Positwinyu's user avatar
15 votes
1 answer
8k views

Enforcing constraints "two tables away"

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short for ...
Snowball's user avatar
  • 327
59 votes
2 answers
58k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
user avatar
5 votes
5 answers
3k views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
Agi Hammerthief's user avatar
30 votes
4 answers
29k views

Retrieving all PK and FK

I have a big database that I need to extract all primary keys and foreign keys from each table. I have pgAdmin III. Is there a way to do this automatically and not go over each table manually?
Nick Ginanto's user avatar
19 votes
1 answer
32k views

Designing a friendships database structure: Should I use a multivalued column?

Say I have a table called User_FriendList, which has the following characteristics: CREATE TABLE User_FriendList ( ID ..., User_ID..., FriendList_IDs..., CONSTRAINT User_Friendlist_PK ...
yeln's user avatar
  • 293
101 votes
3 answers
253k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
Smokey's user avatar
  • 1,121
21 votes
3 answers
19k views

What are the best practices regarding lookup tables in relational databases?

Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column. For example, suppose we have a lookup table called ...
Nishant's user avatar
  • 879
9 votes
2 answers
2k views

Advice on a basic design, first time database design

I am taking a course to become a Java developer. The course involves using databases but we never really design any, unfortunately. Most of the time we get pre-made databases and we have to ...
Vahx's user avatar
  • 225
8 votes
1 answer
706 views

Data inconsistency prohibition if a table refers to another via two many-to-many relationships

I have following database design (by => foreign key constraints are depicted): Company[id] CompanyRealm[id, company_id=>Company.id]many2many Project[id, company_id=>Company.id] ProjectRealm[id, ...
kakabomba's user avatar
  • 183
3 votes
1 answer
2k views

How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

Off the heels of this question, How does MySQL handle joins on ENUMs? How does MySQL work if the referencing table has more and less values in the ENUM than the table being referenced? Is it ...
Evan Carroll's user avatar
  • 63.6k
17 votes
2 answers
19k views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
Ace's user avatar
  • 273
28 votes
4 answers
36k views

Not using foreign key constraints in real practice. Is it OK? [duplicate]

Not using FK constraints is my company's untold rule. FK constraints are used only when designing ERD and not used when creating tables. According to my senior, in real practice, those are very time ...
user2652379's user avatar
26 votes
5 answers
8k views

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like: CREATE TABLE Book ( BookID INT NOT NULL , BookTitle VARCHAR(...
ypercubeᵀᴹ's user avatar
16 votes
4 answers
59k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
Josh's user avatar
  • 163
22 votes
4 answers
8k views

Where should you define foreign keys?

Is it better to define foreign keys in the database or in the code part of an application?
newuser's user avatar
  • 681
19 votes
2 answers
82k views

Find the Foreign Keys Associated with a Given Primary Key

I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via SELECT * FROM INFORMATION_SCHEMA....
MoonKnight's user avatar
17 votes
1 answer
17k views

How to temporarily disable foreign keys in Amazon RDS PostgreSQL?

I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes ...
Piotr Findeisen's user avatar
15 votes
4 answers
6k views

Foreign keys - link using surrogate or natural key?

Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
Callie J's user avatar
  • 492
11 votes
1 answer
5k views

Foreign keys become untrusted after bulk insert

In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet) with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are ...
LowlyDBA - John M's user avatar
4 votes
1 answer
5k views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
Shayma Ahmad's user avatar
0 votes
2 answers
485 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
Michael Underwood's user avatar
0 votes
1 answer
364 views

How to represent it with UNIQUE indexes and foreign keys?

"Group" is a group of pupils. "Topic" is a topic of lessons. To every group corresponds exactly one topic. (So there may be multiple groups per topic.) To a pupil corresponds multiple groups, but ...
porton's user avatar
  • 745
45 votes
3 answers
52k views

Is it acceptable to have circular foreign key references\How to avoid them?

Is it acceptable to have a circular reference between two tables on the foreign key field? If not, how can these situations be avoided? If so, how can data be inserted? Below is an example of ...
KidCode's user avatar
  • 852
15 votes
2 answers
8k views

What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL. What is the purpose ...
Derek Downey's user avatar
  • 23.5k
9 votes
3 answers
83k views

Database design of Room Booking system in a Hotel

Intro and System Description I am currently designing a Database for a Room Booking system in a Hotel. Client fills a Form / Request with following information about Room: amount of people in room ...
rand0rn's user avatar
  • 193
9 votes
2 answers
12k views

Non-unique multicolumn foreign key

I have a "comments" table that models a conversation on a topic, like this: id serial topic_id integer parent_comment_id integer body text So, every comment has a reference to its topic AND ...
Joril's user avatar
  • 441
9 votes
2 answers
3k views

Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

It seems to be disallowed to add a Foreign Key constraint to a foreign table. Is there any other way to do this? My two tables have these constrains on the remote server. More specific details: I ...
odinho - Velmont's user avatar
7 votes
1 answer
11k views

Slow delete caused by many foreign keys

My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the ...
Josh's user avatar
  • 171
6 votes
1 answer
8k views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
Jim Stewart's user avatar
6 votes
1 answer
7k views

Designing a database structure for companies and stock owners

I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the ...
Jane Doe's user avatar
5 votes
2 answers
24k views

Composite primary key from multiple tables / multiple foreign keys

I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this... A great answer was given to building a composite key from multiple foreign ...
Shrout1's user avatar
  • 163
3 votes
2 answers
322 views

Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following: Table father has a composite PK (col1, col2) Table closure has ancestor(a-col1, a-...
thyandrecardoso's user avatar
2 votes
1 answer
14k views

ON DELETE CASCADE not deleting entry for foreign key

I am running the below query in ideone CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) ); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); ...
sdn_world's user avatar
1 vote
1 answer
7k views

Database design for dentists

I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists. Here is a picture of the database design: ...
androidnation's user avatar
0 votes
1 answer
164 views

Why use foreign keys if cascade action is not need?

I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like: CREATE TABLE user ( id int(11) UNSIGNED AUTO_INCREMENT, name ...
Fabiano Araujo's user avatar
173 votes
6 answers
334k views

Good explanation of cascade (ON DELETE/UPDATE) behavior

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is ...
Johntron's user avatar
  • 1,863
28 votes
2 answers
30k views

How to write a query which finds all circular references when a table references itself?

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY (ParentId)...
cubetwo1729's user avatar
24 votes
6 answers
111k views

MySQL FOREIGN KEY constraint is incorrectly formed

I have the following table definition: CREATE TABLE `async_task` ( `idasync_task` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `idasync_type` int(10) unsigned NOT NULL, `priority` tinyint(3) ...
Daniel Gray's user avatar
14 votes
3 answers
2k views

Should I add transitive foreign keys?

Simple example: there is a table of customers. create table Customers ( id integer, constraint CustomersPK primary key (id) ) All the other data in the database should link to a Customer, so e.g....
vektor's user avatar
  • 401
13 votes
3 answers
13k views

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it. quote from MySQL 5.7 Reference Manual / GRANT Syntax REFERENCES ...
Sybil's user avatar
  • 2,518
12 votes
2 answers
2k views

How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t .... Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, ...
Hanno Fietz's user avatar
11 votes
3 answers
238 views

Are there any database engines which will intuit the join condition based on the existing foreign keys?

It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the ...
TML's user avatar
  • 1,364
10 votes
3 answers
32k views

How to copy migrate data to new tables with identity column, while preserving FK relationship?

I want to migrate data from one database to another. The table schemas are exactly the same: CREATE TABLE Customers( [Id] INT NOT NULL PRIMARY KEY IDENTITY, (some other columns ......) ); ...
kevin's user avatar
  • 255
9 votes
2 answers
26k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
dayuloli's user avatar
  • 203
8 votes
1 answer
8k views

How can I create a Foreign Key to a Compound Primary Key with a single column?

I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, ...
Ehryk's user avatar
  • 621
7 votes
2 answers
4k views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
Jack Douglas's user avatar