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.

133 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
2k views

Deferrable Foreign Key Constraints

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE? This answer on StackOverflow mentions the performance impact that comes with no ...
4
votes
2answers
2k views

Two ways to define may-have-a (1..0-1) relationship

Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways: Let A be the "main" table (left side of ..) and B be the "...
4
votes
0answers
2k views

Why is adding a FK constraint slow in MySQL?

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand. CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY `pk_A` (`...
3
votes
0answers
210 views

Query for getting reserved seats in a cinema

I have this cinema project for a course. And I have to visually show the seats to user so they can reserve their preferred seats. I need to get the reserved seats from the database of that particular ...
3
votes
1answer
131 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): ...
3
votes
0answers
190 views

Orphan records after delete statement in stored procedure

I have a database where some of the tables have parent --> child --> grandchild relationships through foreign keys as shown below. CREATE TABLE Parent ( ParentID INT UNSIGNED PRIMARY KEY ...
3
votes
0answers
1k views

Supertype table with disjoint subtypes in MySQL

I want to have base parent table with few disjoint subtypes. To illustrate what I mean: CREATE TABLE issues ( issue_id SERIAL PRIMARY_KEY, issue_type CHAR(1) CHECK (issue_type IN ('B', 'F')), ...
3
votes
0answers
41 views

If a relationship is (logically) deleted, is this marked in the link table or just the satellite table(s)?

I'm learning about the Data Vault data warehousing approach, and so far the examples I've seen have a DateLoaded or similar field in all tables (hub, link, and satellite), but a DateExpired or EndDate ...
3
votes
0answers
175 views

Slowly changing dimensions - data integrity

When using SCD is it possible to still enforce FK contraints. Consider the following table structure: customer (data_version_id PK, customer_id PK, name, address1,...etc) order(order_id PK, ...
3
votes
2answers
42 views

Reasons to maintain FKs on an empty, deprecated table?

So I have a few dozen empty tables with foreign keys to other, populated tables. These tables are not being accessed by any CRUD operations, but have about 6-12 months before they can be unlinked from ...
3
votes
1answer
2k 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'...
3
votes
1answer
565 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)....
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: ...
2
votes
0answers
188 views

Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?

I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation. First, take a look at this question I asked on Stack Overflow ...
2
votes
0answers
399 views

postgresql handle conflict on delete

I have a table that looks roughly like this: create table people ( id bigserial primary key, name varchar, deleted timestamp with time zone, parent bigint references people(id) ...
2
votes
0answers
50 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
2
votes
0answers
54 views

SQL Server database design with foreign keys

See the following link for the (total) databse design: https://sqldbm.com/Project/SQLServer/Share/_AUedvNutCEV2DGLJleUWA I am not used to the database design first and then build it. But... for ...
2
votes
1answer
59 views

One status table for all models?

lets say I have a users,posts and categories tables .. users table would have statuses like ( active, expired .. etc ) posts table would have statuses like ( published, draft, archived .. etc ) ...
2
votes
1answer
84 views

Create foreign key constraint on indirect data

I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database: CREATE TABLE logger ( id INTEGER PRIMARY KEY ); CREATE TABLE ...
2
votes
0answers
178 views

Should I move repeating foreign keys into separate table?

Idea Users can upload lots of items and send them from processing. From the upload settings I can set a channel to which the uploaded items belong to. Channel is a separate entity. Currently I set ...
2
votes
0answers
68 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. Activities ...
2
votes
0answers
342 views

Compound key that only allows nulls if they are null in the referenced table

I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the ...
2
votes
0answers
322 views

Why are foreign keys ignored on subscribers?

We are using merge replication on SQL Server 2008 R2. There are many tables with foreign keys providing a logical dependence on each other. For this question, let's say there are User and File tables, ...
2
votes
0answers
1k views

AFTER DELETE trigger will not fire during a cascade delete

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends. Aisle Table: Aisle char(4) Start_Node ...
2
votes
0answers
51 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
2
votes
1answer
1k views

Does the order I declare foreign key constraints in matter?

Does it matter what order I add foreign key constraints to a table in, or what order I write them in a CREATE TABLE statement? MySQL clearly at least remembers the order; if I run the DDL statements ...
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 ...
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
107 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 ...
1
vote
0answers
24 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
123 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 ...
1
vote
1answer
86 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 ...
1
vote
1answer
131 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 ...
1
vote
0answers
38 views

Weird MySQL column name issue

After trying to use the Propel PHP ORM on my database, I noticed that there is an issue with foreign keys. This is a table with foreign keys: CREATE TABLE `appusers` ( `AppID` INT(11) NOT NULL, `...
1
vote
0answers
27 views

Strategy to manage foreign key from another system/database

There is a system in the company which stores all the consultants working for the company. Our project needs to store the consultants assigned to clients. We use the id of the consultant in our ...
1
vote
0answers
39 views

Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] ...
1
vote
0answers
3k views

errno: 150 “Foreign key constraint is incorrectly formed”, I cant figure it out

I've been trying to get this to work for a while and I've found plenty of examples to compare it too. Yet no matter what I try I get the same error. Can you guys please take a look at this and point ...
1
vote
0answers
32 views

Ideal and Efficient Way of Handling User Logins with Multiple Types (i.e. Employee, Client, etc.)?

I'm developing a database where I will have two types of users: Employee and Client. I'm thinking it would be ideal to just have one big USERS TABLE with a user_ID as PK and Type column to ...
1
vote
0answers
83 views

Associate multiple tables with a single table

I have a RDBMS database and I need to associate a column in a new table to multiple other existing tables whose schema can't be changed. The new table looks something like this, UUID(pk) PartId(pk)...
1
vote
0answers
125 views

How to find which foreign key constraints a table row is associated with?

(There are similar sounding questions out there at first glance, but none any that are actually identical from what I've seen so far) I'm on PostgreSQL 9.4.7 In narrow terms of my use case (good ...
1
vote
0answers
53 views

Foreign key constraint across tables

I have tables A, B and C where there is a one-to-many relationship between B and A and B and C respectively. I need a constraint that for every row of C rowC.propertyC is element of SELECT A....
1
vote
2answers
3k views

Mysql : foreign key relative to multiple tables

I'm trying to improve the database integrity adding some foreign key (it's the first time i use them). I have some tables relative to different type of devices (different tables because the "devices" ...
1
vote
1answer
566 views

Update of db yields “Error Code: 1215. Cannot add foreign key constraint”

A phpmyadmin export of a remote MySQL db is used to update a local instance of the db. Disable foreign key checks is selected. Yet running the export either as a phpmyadmin import or as a script in ...
1
vote
1answer
66 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 ...
1
vote
2answers
204 views

What is the most convenient indexing strategy for two foreign key constraints?

Which of the two following options is more convenient for indexing the relevant foreign keys? Option A: Create Table table3( t3_id int not null auto_increment, t1_id int not null, t2_id int ...
1
vote
0answers
337 views

PostgreSQL - Violate foreign key on purpose

I have an app 1.0 that uses a database called let's say foo of at least 150 tables filled with data. I have upgraded the app to 3.0 and now I also have a database called foo2 which has almost the same ...
1
vote
1answer
142 views

Mysql auto insert record in primary table if not exist

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key? For example, assume these tables: - user(id, name, age) - topic(id, name) - ...
1
vote
0answers
78 views

How to reference Foreign Key(s) to the parent table where data goes on increasing?

Hello i am very new to learning PHP and MySQL. I have just begun learning MySQL. As the question mentioned above, will be kind if anyone can give a solution. Please see the example of a simple ...
1
vote
0answers
59 views

My simple FK constraints appear to have been ignored. How?

I have two tables, device and platform where device.platform_id enforces the following constraint: Foreign-key constraints: "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(...
1
vote
0answers
551 views

Foreign key violations detected upon UPDATE on another column

I have a table country that is referenced by many tables like so: db=# \d client Table "public.client" Column | Type | Modifiers --...