Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: Delete Primary Key that Foreign Key references without having to delete Foreign Key

    I'm sure the answer to this is obvious, but it's really late and my brain is fried. Is it possible to have it so you can delete a record from one table even though it is referenced in another table with a Foreign Key, without having to delete the record in the Foreign Key table associated with it first?

    I have a bunch of tables that are used to record when a user creates or updates records in various tables. It references the user's name as a Foreign Key and the Primary Key from the table that contains the data they inserted/updated as a Foreign Key. I want to be able to keep the data in the table even if the user and/or the data they created is deleted... Any suggestions would be greatly appreciated.
    Paul Palubinski

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    instead of ON DELETE NO ACTION or ON DELETE CASCADE, declare your foreign keys with ON DELETE SET NULL (SQL 2005 and up)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, you cannot.
    You will need to remove the physical foreign key relationship and just maintain a logical one, or archive the child records to a separate table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    See if I get this right.

    You have a number of correlated tables. The main table MainTable stores the data, the second table stores the name of the person who created the record in MainTable and the PK of the record in MainTable. And you declare (at least) two FK's in the second table, one to the PERSONS table another to MainTable.

    I want to be able to keep the data in the table even if the user and/or the data they created is deleted
    Why would you want to delete the person's name from the PERSONS table? Simply mark that persons record as inactive with an extra column
    Code:
    isActive CHAR(1) NOT NULL -- or use the BIT data type if you don't mind incompatibility
         CONSTRAINT person_iasActive CHECK (isActive IN('Y', 'N'))
    After the deletion of the data record in MainTable, what would be the added value of keeping a record with a FK that points to the Big Void? To be able to count the number of records someone has ever created?

    Can you describe what you are willing to accomplish with this design?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937 View Post
    instead of ON DELETE NO ACTION or ON DELETE CASCADE, declare your foreign keys with ON DELETE SET NULL (SQL 2005 and up)
    Don't forget ON DELETE SET DEFAULT
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937 View Post
    instead of ON DELETE NO ACTION or ON DELETE CASCADE, declare your foreign keys with ON DELETE SET NULL (SQL 2005 and up)
    He wants to keep the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by Wim View Post
    See if I get this right.

    You have a number of correlated tables. The main table MainTable stores the data, the second table stores the name of the person who created the record in MainTable and the PK of the record in MainTable. And you declare (at least) two FK's in the second table, one to the PERSONS table another to MainTable.

    Why would you want to delete the person's name from the PERSONS table? Simply mark that persons record as inactive with an extra column
    Code:
    isActive CHAR(1) NOT NULL -- or use the BIT data type if you don't mind incompatibility
         CONSTRAINT person_iasActive CHECK (isActive IN('Y', 'N'))
    After the deletion of the data record in MainTable, what would be the added value of keeping a record with a FK that points to the Big Void? To be able to count the number of records someone has ever created?

    Can you describe what you are willing to accomplish with this design?
    That's a good question. One I don't really have an answer to. The remaining data in the records table would be useless. The more I think about it, the more I realize how useless it would be for me to design the DB the way I described. Also, I don't really have much use for recording every single time a particular entity was updated. All I really need is to know when it was created and the last time it was updated (and by who)...
    Paul Palubinski

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    In every table I create, I add 4 or 5 NOT NULL "bookkeeping" columns:
    Code:
    CreatedBy 	CHAR(n) 	NOT NULL,
    CretedOn	DATETIME	NOT NULL  DEFAULT GetDate(),
    ModifiedBy	CHAR(n) 	NOT NULL,
    ModifiedOn	DATETIME	NOT NULL  DEFAULT GetDate(),
    (NrModified	INT		NOT NULL  DEFAULT 1)
    But I don't define FK constraints on the user names. A FK doesn't come for free, the DBMS has a number of things to to to enforce the constraint, slowing your system a bit. The only constraint I define on them is a NOT NULL constraint, that should be enough.

    These bookkeeping columns can be set and updated in the SPs that create and update the records or by triggers defined on the table.

    The name of the user should be passed to the SP or DML SQL. Depending on your user naming convention, the "n" in CHAR(n), can be a short or larger number.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Wim View Post
    A FK doesn't come for free, the DBMS has a number of things to to to enforce the constraint, slowing your system a bit.
    Not a good reason for neglecting to enforce referential integrity.

    In this case, however, there is a good reason based on sound design. These are auditing columns, and should be treated as snapshots of a point in time. If the name of the user who created them changes, or the user's record is deleted from the database, we may still need to know what records they created. The CreatedBy column should not have a foreign key to the Users table because it is solely an attribute of its own table, and is independent of the user record.
    Another case where this same issue crops up is invoices. If a customer wants a copy of an invoice they received a year ago, they want it EXACTLY as it was generated before, not updated to reflect they fact that the woman who approved the purchase got married and changed her last name, or the client's address changed when they relocated their headquarters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by Wim View Post
    In every table I create, I add 4 or 5 NOT NULL "bookkeeping" columns:
    Code:
    CreatedBy 	CHAR(n) 	NOT NULL,
    CretedOn	DATETIME	NOT NULL  DEFAULT GetDate(),
    ModifiedBy	CHAR(n) 	NOT NULL,
    ModifiedOn	DATETIME	NOT NULL  DEFAULT GetDate(),
    (NrModified	INT		NOT NULL  DEFAULT 1)
    But I don't define FK constraints on the user names. A FK doesn't come for free, the DBMS has a number of things to to to enforce the constraint, slowing your system a bit. The only constraint I define on them is a NOT NULL constraint, that should be enough.

    These bookkeeping columns can be set and updated in the SPs that create and update the records or by triggers defined on the table.

    The name of the user should be passed to the SP or DML SQL. Depending on your user naming convention, the "n" in CHAR(n), can be a short or larger number.
    That's similar to how I was doing it before, but I didn't like having to add those "bookkeeping" columns for every table, so I thought maybe I'd have a separate "bookkeeping" schema and put tables in there dedicated to recording that information. But it was a bad idea, and now I'm seeing that doing it your way is the best way to go. So now I get to have the pleasure of changing all the tables in my database and then changing all the code in my business layer to match those changes. It's going to be so much fun. Thanks for your response though. It totally makes sense and was a tremendous help.
    Paul Palubinski

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by ppalubinski View Post
    I have a bunch of tables that are used to record when a user creates or updates records in various tables. It references the user's name as a Foreign Key and the Primary Key from the table that contains the data they inserted/updated as a Foreign Key. I want to be able to keep the data in the table even if the user and/or the data they created is deleted... Any suggestions would be greatly appreciated.
    I think what you are saying is that you don't want to enforce the foreign key because the constraint doesn't apply to the data in that table. So don't enforce it. Drop the foreign key since it apparently isn't achieving anything useful for you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •