Skip to main content
52 votes
Accepted

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

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits. The point of a foreign key (FK) is to ensure that this ...
Michael Green's user avatar
32 votes
Accepted

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

Managing an individual piece of information Assuming that, in your business domain, a User can have zero-one-or-many Friends; a Friend must first be registered as a User; and you will search for, and/...
MDCCL's user avatar
  • 8,520
30 votes

MATCH FULL vs MATCH SIMPLE in foreign key constraints

FULL vs SIMPLE vs PARTIAL While the chosen answer is correct, if this is new to you, you may want to see it with code -- I think it's easier to grok that way. -- one row with (1,1) CREATE TABLE foo (...
Evan Carroll's user avatar
  • 63.7k
26 votes
Accepted

PostgreSQL multiple column foreign key

A foreign key can also constrain and reference a group of columns. As usual, it then needs to be written in table constraint form. Here is a contrived syntax example: CREATE TABLE t1 ( a integer ...
DEarTh's user avatar
  • 757
24 votes
Accepted

Cannot drop column : needed in a foreign key constraint

Having a look at MySql docs I've found a warning about foreign_key_keys: Warning With foreign_key_checks=0, dropping an index required by a foreign key constraint places the table in an ...
McNets's user avatar
  • 23.8k
23 votes

How to add a column with a foreign key constraint to a table that already exists?

I'm not sure why everyone is telling you that you have to do this in two steps. In fact, you don't. You tried to add a FOREIGN KEY which assumes, by design, the column is there and throws that error ...
Evan Carroll's user avatar
  • 63.7k
22 votes
Accepted

MySQL FOREIGN KEY constraint is incorrectly formed

Both the referencing and referenced columns must be of the same type (and same in this case includes the unsigned attribute). You didn't define notification.async_task_id as unsigned, so it was ...
ypercubeᵀᴹ's user avatar
20 votes
Accepted

Why does PostgreSQL allow certain type mismatches in Foreign Keys?

Use the source, Luke! In ATAddForeignKeyConstraint in src/backend/commands/tablecmds.c, we find the truth about the requirements: /* * There had better be a primary equality operator ...
Laurenz Albe's user avatar
  • 53.5k
19 votes

Good explanation of cascade (ON DELETE/UPDATE) behavior

SQL:2011 Spec There are five options for ON DELETE and ON UPDATE that can apply to the FOREIGN KEY. These are called <referential actions>, directly from the SQL:2011 spec ON DELETE CASCADE: if ...
Evan Carroll's user avatar
  • 63.7k
18 votes

Restoring from pg_dump with foreign key constraints

You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work ...
achimh's user avatar
  • 288
18 votes

The use of multiple foreign keys on same column in SQL Server

SQL Server allows you to do a lot of silly things. You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the ...
Martin Smith's user avatar
  • 85.1k
17 votes
Accepted

MariaDB: disable foreign key checks

It should work with: SET FOREIGN_KEY_CHECKS=0 I tried the following: create table parent (x int not null primary key) engine = innodb; create table child (x int not null primary key, constraint aaa ...
Lennart - Slava Ukraini's user avatar
17 votes
Accepted

Why does dropping foreign keys take long?

Dropping a constraint requires a Sch-M (Schema Modification) lock that will block others to query the table during the modification. You are probably waiting to get that lock and has to wait until all ...
Mikael Eriksson's user avatar
17 votes
Accepted

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

You probably receive the error because you have orphaned records in the [rpt].[ReportLessonCompetency] table. An orphaned record is a record in a child table without a corresponding parent record in ...
Thomas Costers's user avatar
15 votes
Accepted

Why do composite foreign keys need a separate unique constraint?

It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a UNIQUE constraint on (a1, a2), we can't add a FOREIGN ...
ypercubeᵀᴹ's user avatar
15 votes
Accepted

MySQL - Delete row that has a foreign key constraint which reference to itself

Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider: Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I ...
ypercubeᵀᴹ's user avatar
15 votes
Accepted

The use of multiple foreign keys on same column in SQL Server

There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value. The ...
Dan Guzman's user avatar
  • 28.3k
14 votes

Retrieving all PK and FK

No need to parse pg_get_constraintdef(), just use columns of pg_constraint table to obtain other details (the docs). Here constraint_type can be: p - primary key, f - foreign key, u - unique, c - ...
Evgeny Nozdrev's user avatar
13 votes
Accepted

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

By IDN, I take it you mean an IDENTITY, SEQUENCE or AUTO_INCREMENT field? You should take a look here and here. Note, section 5 (Misusing Data values as Data Elements) of the first reference, ...
Vérace's user avatar
  • 30.2k
13 votes

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

There is a third approach which has some of the advantages of your two options - put an actual code in the code table. By this I mean a short character sequence that captures the essence of the full ...
Michael Green's user avatar
13 votes
Accepted

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

Here's a way that scales easily to three related tables. Use MERGE to insert the data into the copy tables so that you can OUTPUT the old and new IDENTITY values into a control table and use them for ...
Mister Magoo's user avatar
  • 3,543
13 votes

What is a "Partial Matching Index"?

I spoke to people much smarter than I and we will be documenting this soon™. The actual definition of this, in the interim is: PartialMatchingIndexCount reflects the number of references that ...
Sean Gallardy's user avatar
13 votes

The use of multiple foreign keys on same column in SQL Server

There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns. It's like having the same check 2 or more times.
ypercubeᵀᴹ's user avatar
13 votes

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

You can indeed do this. If you scroll lower down on the linked postgres documentation, you'll see: Sometimes it is useful for the “other table” of a foreign key constraint to be the same table; this ...
Alexandra Feldman's user avatar
12 votes

Retrieving all PK and FK

Based on Erwin solution: SELECT conrelid::regclass AS "FK_Table" ,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in ...
profimedica's user avatar
12 votes
Accepted

Find all tables without foreign keys in a database

SwapnilBhate's answer will return tables that are referenced by a foreign key. Adding a further LEFT JOIN will exclude these: SELECT tbl.name FROM sys.tables AS tbl LEFT JOIN sys....
Sam's user avatar
  • 146
12 votes
Accepted

Postgres: Read CONSTRAINTS definition

This solve my problem SELECT conrelid::regclass AS table_from ,conname ,pg_get_constraintdef(c.oid) FROM pg_constraint c JOIN pg_namespace n ON n.oid = c.connamespace WHERE ...
Ephra's user avatar
  • 141
11 votes

MySQL - Delete row that has a foreign key constraint which reference to itself

SET FOREIGN_KEY_CHECKS=0; DELETE FROM forum WHERE Owner_Id = 1 ORDER BY nright; SET FOREIGN_KEY_CHECKS=1; just not forget in this case You must manually parse situations when parent_id show to 1, ...
a_vlad's user avatar
  • 3,655
11 votes
Accepted

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

The docs say this about NOT VALID ADD table_constraint [ NOT VALID ] This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently ...
Evan Carroll's user avatar
  • 63.7k

Only top scored, non community-wiki answers of a minimum length are eligible