Skip to main content
6 votes
Accepted

Are individual indexes necessary on columns that are included in a compound primary key?

An index can always be used by just a leading key. So the IX_RecipientsDonors_RecipientId index is not really necessary, as the primary key already covers the same columns. While it will slightly ...
Charlieface's user avatar
  • 13.3k
4 votes
Accepted

How can I know how many tables my DELETE has changed?

If you’re just generally curious, look at the query plan. It will show all the tables involved. If you’re looking to log or audit them, that’s a different story. You would need to use triggers, or ...
Erik Darling's user avatar
  • 41.8k
4 votes
Accepted

CASCADE DELETE per statement

There is no way to automatically delete all dependent rows; you will have to delete them before you delete the referenced row.
Laurenz Albe's user avatar
  • 53.4k
4 votes
Accepted

Design: What should be my PK, FK etc

Each "main" Entity (or Table) represents instances (i.e. Records) of an object that can exist in their own right, in the absence of any other Entity, e.g. a Person. The attribute that ...
Phill  W.'s user avatar
  • 8,850
3 votes
Accepted

Why am I expected to use parentheses notation instead of regular dot notation when defining foreign key relationships?

Because the source and target of a foreign key is a tuple not a column. Eg the target table may have a compound key, and the foreign key would look like: foreign key (TenantId, Id) references users(...
David Browne - Microsoft's user avatar
3 votes
Accepted

What foreign key should I store from users table or clients table?

... only a client can buy a course ... Bingo! There you have a Business Rule that states that the relationship is between [Purchased_]Course and Client, not [Purchased_]Course and User. So you would ...
Phill  W.'s user avatar
  • 8,850
3 votes

Better way to return list of FK "parent" tables?

Just the list of parent tables and column names. "Children" I fixed the terminology. What you display are the tables referencing the input table. So "children", not "parents&...
Erwin Brandstetter's user avatar
3 votes

Why should I manually name foreign key constraints?

People care because constraint violations typically display the constraint name, and so it's helpful to know that you violated a constraint called, eg fk_person_state rather than one called ...
David Browne - Microsoft's user avatar
2 votes
Accepted

Delete parent without deleting children

My problem is when a Stocked batch runs out, I want to delete the record of it, while keeping all the records of its sale. How would I manage that with a foreign key? The whole purpose of a foreign ...
J.D.'s user avatar
  • 38k
2 votes
Accepted

How to disable FOREIGN KEY referential integrity trigger in PostgreSQL without disabling other triggers in table?

First, you will have to find out the names of the system triggers on the table: SELECT tgname, tgtype FROM pg_trigger WHERE tgrelid = 'myschema.mytable'::regclass AND tgisinternal; ...
Laurenz Albe's user avatar
  • 53.4k
2 votes

Why should I manually name foreign key constraints?

If you ever refer to FKs in manually written maintenance scripts (less common these day as many frameworks do a lot of this sort of thing automagically, but far from rare) it is useful to follow a ...
David Spillett's user avatar
2 votes
Accepted

How do you only get data from the table with the foreign key using SQLite?

The secret is in double quotes: https://www.sqlite.org/lang_keywords.html A string in double-quotes is an identifier. It is used primarily for cases where you have field name with a space inside or ...
White Owl's user avatar
  • 830
2 votes

How do I write sensible FOREIGN KEY CONSTRAINTS if the PRIMARY KEYS from two tables can reference multiple of the other table's PK

I am reluctant to give the "users" table several "workgroup" columns (like "workgroup1", "workgroup2", etc) and equally reluctant to give the "workgroups&...
Phill  W.'s user avatar
  • 8,850
2 votes

How can I know how many tables my DELETE has changed?

... I could delete one row from one table in a sufficiently evil database and unknowingly delete 99% of the database's data. Absolutely correct. One of the biggest threats to our databases is the &...
Phill  W.'s user avatar
  • 8,850
2 votes

How can I know how many tables my DELETE has changed?

You could get this information out of the Actual query plan. If you want to parse the query plan to find out how many rows were actually affected, you can do it like this. WITH XMLNAMESPACES ( ...
Charlieface's user avatar
  • 13.3k
2 votes

Design: What should be my PK, FK etc

If you're new to sql: (set, kopf, time) is a tuple. Saying (set, kopf, time) is unique means there can be only one row with the specific values (set, kopf, time). It does not mean any of the columns ...
bobflux's user avatar
  • 1,551
2 votes

How to change insert values from 0 to NULL for a specific field in order to avoid foreign key violation?

The most concordant solution would be to change the front-end application so that, in the first place, it inserts Null instead of 0. If this isn't possible, it might be relevant to explain why as part ...
Steve's user avatar
  • 511
2 votes
Accepted

Foreign Key Error in SQLite3 CREATE TABLE Command

You need also the column PersonID in the table conversations, as it the value of the reference and the Foreign keyis only a constraint for the column PersonID. It indicates that you can add only ...
nbk's user avatar
  • 8,434
1 vote
Accepted

primary key referened by only single foreign key of another table among multiple tables that can reference it

There are no constraints to do that in any RDBMS I know... Traditional way is just go outside of the database. Have a field admin_or_customer in the account table and let client application to read ...
White Owl's user avatar
  • 830
1 vote

A foreign key that references a table and another to reference the parent table (what's the alternative)

The simplest solution is to remove ti_id from your user_permissions table. If user_permissions references some tim_id's, and each respective trackable_item_metrics references exactly one ti_id, so you ...
Bill Karwin's user avatar
  • 15.1k
1 vote
Accepted

one to one relationship on tables

Sqlare brackets are a sign that you copy your code from a site that explain how to do it in SQL Server. For mysql you need backticks to escape reserved words and the syntax is also different CREATE ...
nbk's user avatar
  • 8,434
1 vote

Can I display more than one column as foreign value description in phpMyAdmin?

phpMyAdmin doesn't offer this feature, but you could get the result you want by creating a virtual column in the foreign table that displays the information you want to see and setting that in ...
rlpvl's user avatar
  • 11
1 vote
Accepted

SQL Server using table's Primary Key as part of Foreign Key

When you add the constraints required for identification purposes and row uniqueness, your model is something like this: If you had not shot yourself in the foot by using surrogates as the first ...
Giannis Tsimarakis's user avatar
1 vote
Accepted

What is the correct sentence to express the relationship between the user and his direct manager:

So your actual question seems to be, "how can I be confident that my answer is correct?" You have a working hypothesis, which doesn't seem wrong at the first glance: a reference to the ...
mustaccio's user avatar
  • 26.2k
1 vote

CASCADE DELETE per statement

ALTER and UPDATE each child table to have a copy of the field bar from its parent record in foo. DELETE all rows in each child table where bar = 'baz'. Clean up by ALTER DROP COLUMN on the new fields ...
Bruce's user avatar
  • 111
1 vote
Accepted

Postgres: does marking a Foreign Key constraint NOT VALID affect query planning?

The optimizer uses foreign key constraints less than you might think. They can improve the row count estimate for a join. That won't improve the performance of the join, but a good row count estimate ...
Laurenz Albe's user avatar
  • 53.4k
1 vote

Can a foreign key refer to a subset of parent table's rows? (Value domain constraint)

You can accomplish this by adding a set of dummy columns for your domain literals and using a set of two-column FKs that use them. Invisible virtual columns are best as they wouldn't be visible to ...
Paul W's user avatar
  • 633
1 vote

Keep the foreign key constraint using soft delete

You could make <primary key> plus <isdeleted> a composite unique key, and reference that in the foreign keys instead of the primary key, though this would feel a bit dirty to me. You would ...
David Spillett's user avatar
1 vote

Why is adding a FK constraint slow in MySQL?

CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY (`pk`), KEY `idx_fk` (`fk`) ) ENGINE=INNODB; SET foreign_key_checks=0; ALTER TABLE `A` ADD CONSTRAINT `...
warashi nguyen's user avatar
1 vote

Foreign keys - link using surrogate or natural key?

Older question but still very relevant to data architecture. The debate to go surrogate or go natural is ongoing and of course it depends. Very good points made for both sides here and other questions ...
Alexander Kirstein's user avatar

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