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 ...
- 25.1k
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 (...
- 64.6k
27
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 ...
- 777
25
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 ...
- 64.6k
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 ...
- 23.9k
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 ...
- 98.6k
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 ...
- 56.4k
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 ...
- 64.6k
19
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 ...
- 298
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 ...
- 86.5k
18
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 ...
- 738
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 ...
- 22.2k
16
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 - ...
- 553
16
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 ...
- 98.6k
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 ...
- 98.6k
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 ...
- 28.5k
15
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 ...
14
votes
Accepted
PostgreSQL - How to list all foreign key link to Primary key of a table
You can query the catalog tables:
SELECT la.attrelid::regclass AS referencing_table,
la.attname AS referencing_column
FROM pg_constraint AS c
JOIN pg_index AS i
ON i.indexrelid = c....
- 56.4k
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 ...
- 3,573
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 ...
- 35.5k
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.
- 98.6k
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 ...
- 221
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....
- 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 ...
- 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, ...
- 3,665
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 ...
- 64.6k
11
votes
Accepted
Disable foreign key checks during import
The command
SET FOREIGN_KEY_CHECKS=0;
sets the value of a variable in a session scope. So it affects on the current session only, and do not affect on another sessions, both already running and ...
- 20.3k
10
votes
Accepted
the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?
If you have the ability to create a foreign key constraint, you are actually getting permission to check for the existence/non-existence of a certain value in a certain column (or set of columns) of a ...
- 13.6k
10
votes
Accepted
Create foreign key on foreign table in postgresql
You can't create a FOREIGN KEY constraint that references a table in either a different database or through a foreign data wrapper. A foreign key must reference a (base) table* in the same database (a ...
- 98.6k
Only top scored, non community-wiki answers of a minimum length are eligible