For that, no. As soon as you have links in parent_id, you'll need to determine how you want to alter that table, but mariadb allows the value to be null.
Given I don't know what future alters you'll execute, I would only add FOREIGN_KEY_CHECKS=0. if fails without, and based on your assessment of the data to be altered. NULLS have consequences. Thats my main point. I would not worry about the performance concerns, as you restated them. Its going to be a zero sum game. On Fri, Dec 20, 2024 at 3:43 PM Guillermo Céspedes Tabárez < [email protected]> wrote: > Hi Jeff, > Thank you for your response! > > To clarify, my question is more hypothetical and focused on > understanding how the database engine handles this specific scenario. > > If the column being added is new and starts with all values as NULL, > does it make sense to temporarily disable FOREIGN_KEY_CHECKS during > the ALTER TABLE operation? My main concern is whether the database > performs any additional checks or processes, even though the column is > empty (NULL), or if the operation is already optimized and skipping > the checks automatically. > > I'm not debating the use of foreign keys or their impact on runtime > performance. I'm specifically curious if disabling FOREIGN_KEY_CHECKS > in this situation saves any resources during the ALTER TABLE, or if > it's unnecessary because the database doesn't do anything in this case > that would benefit from skipping checks. > > Best regards, > Guillermo > > El vie, 20 dic 2024 a la(s) 5:14 p.m., Jeff Dyke ([email protected]) > escribió: > > > > While i'm not really sure what performance issues you are referring to. > FK are going to have a small performance hit on row changes, but for data > integrity, its what you need. When you start to design FKs with Null > values, you are talking some of the work off of the database work that can > be done for you, and moving it to your developers. Also parent_id should > be indexed to assist in select performance. > > > > Most times that I have seen this done in the past, its normally removed > and the index is kept. if parent_ids are going to be duplicated and only > null for a period of time, that is a good one to many relationships, that > will enforce integrity once populated. > > > > If you think this will improve overall performance, that is not the goal > of Foreign Keys. > > > > > > On Fri, Dec 20, 2024 at 11:03 AM Guillermo Céspedes Tabárez via discuss < > [email protected]> wrote: > >> > >> Hi, > >> > >> I’d like to understand the performance impact of adding a FOREIGN KEY > >> constraint to a new column in a large table. If the column is nullable > >> and defaults to NULL, does the engine perform any checks or > >> validations on existing rows? Or is the operation optimized since the > >> column starts with all values as NULL? > >> > >> Here’s a simplified example: > >> > >> ALTER TABLE child_table > >> ADD COLUMN parent_id INT NULL, > >> ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES > >> parent_table (id) > >> ON UPDATE CASCADE ON DELETE SET NULL; > >> > >> Both child_table and parent_table are large, and the new column > >> (parent_id) is empty initially. > >> > >> Does adding the foreign key have any measurable performance impact in > >> this case? Additionally, would it make sense to temporarily disable > >> FOREIGN_KEY_CHECKS for this ALTER TABLE operation and then re-enable > >> it? Could this save any resources or improve performance? > >> > >> Thanks for any insights! > >> > >> Best regards, > >> Guillermo. > >> _______________________________________________ > >> discuss mailing list -- [email protected] > >> To unsubscribe send an email to [email protected] >
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
