Databases
StackOverflow2013
Postsdbo
POCheck SQL reference constraint on INSERT but not on DELETE
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCheck SQL reference constraint on INSERT but not on DELETE
    primarykey
    data
    text
    <p>I have a Logs table in my database where I'm storing records of user actions. There's a column for UserID, which is foreign-keyed to the Users table to check that the UserID exists when a log is added. However, attempting to delete a user causes a constraint failure because it leaves a UserID in the Logs table that no longer exists in the Users table. Deleting the offending rows from Logs isn't an option, because the whole point is to have persistent records (so that we can find out who broke what, basically) so deleting all actions by that user rather defeats the purpose.</p> <p>Is there a simple way to have it check the constraint when inserting new logs (to make sure the user exists at the time) but not when deleting a user? I'm accessing the DB via LINQ, so my queries are in C#, not SQL, so anything that requires that is out, but I have access to the database through MS SQL Server Management Studio, so I can do what I like with it.</p> <p>EDIT for slight clarification: the value of knowing who made an action is not just in accountability; it's useful in determining if an issue is user-specific, organisation-specific, or system-wide, which can greatly narrow down the list of possible causes. Once a user is deleted, it's still worth keeping their actions, and it's still useful to know which actions were by that user, for various reasons - for example, when tracking down an issue caused by one user doing a certain combination of things (eg. two actions that are fine separately but cause errors when one immediately follows another).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USanaximander
    1. USanaximander
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COyou can remove the constraints (keeping the column) and only check in your code if the user exists
      singulars
      1. POCheck SQL reference constraint on INSERT but not on DELETE
      1. USYoav
    2. CO@Yoav I thought of trying that, but it would require the logs repository to access the users repository, which is against the repository pattern we're using. Repositories aren't meant to touch each other. Although I guess I could make it a logs service, with a logs repository and a users repository, which wouldn't go against pattern.
      singulars
      1. POCheck SQL reference constraint on INSERT but not on DELETE
      1. USanaximander
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload