Use ON CASCADE DELETE in SQL

Asked

Viewed 503 times

0

I have two tables in SQL, a call Patient and another Diagnosis, I can do all the operations with the diagnosis (insert, edit and delete), but I just wish it was possible to delete diagnoses that were not associated with a patient.

FIRST QUESTION: I will need to create a Foreign key for diagnosis in the sick table?

SECOND QUESTION: how should I use ON CASCADE DELETE to ensure that the diagnosis is not erased if a patient already has this diagnosis?

MY TABLES IN SQL:

CREATE TABLE [dbo].[Doente] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [numero]         INT            NOT NULL,
    [nome]           NVARCHAR (MAX) NOT NULL,
    [sexo]           NVARCHAR (50)  NOT NULL,
    [dataNascimento] DATE           NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Diagnostico] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [descricao] NVARCHAR (MAX) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
sql

  • What is the relationship between Diagnosis and Patient? A patient may have more than one diagnosis or is a diagnosis that may have more than one patient?

  • The goal is that a patient may have several diagnoses and that a diagnosis may also be associated with several patients...

  • If you add the On Cascade Delete command in the table creation, whenever you delete in the Parent table, it will be deleted in the child(s) table(s). This has a cost, maybe not so expensive, but has. As to your condition, you can create an SP to do this check and then delete. I guess that’s it.

  • http://magaweb.com.br/blog/incluindo-o-comando-on-delete-cascade-em-uma-tabela/

2 answers

1


If you want there to be a relationship Many-to-Many amid Diagnosis and Sick it is necessary to define another table to represent that relation.

This table should have 2 fields one to store the diagnosis ID and another the patient ID.

Both should be Foreign key to ensure that a patient who has a diagnosis or diagnosis who has a patient cannot be eliminated.

The ON CASCADE DELETE does not apply to what you intend.

1

I have two tables in SQL, one called Sick and another Diagnostic, I can do all operations with the diagnostic(insert, edit and delete), but I just wish it was possible to delete the diagnoses that were not associated with a patient.

FIRST QUESTION: I will need to create a Foreign key for diagnosis in sick table?

Yes, in a 1-to-1 ratio, in other cases read the ramaral.

SECOND QUESTION: how should I use ON CASCADE DELETE to ensure that the diagnosis is not erased if a patient already has this diagnosis?

Never, first that this command no longer works on MSSQL Server, second that it was created with the opposite purpose, ie it "circumvents" the constraints by deleting the dependencies first.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.