Use ON CASCADE DELETE in SQL

Question

Use ON CASCADE DELETE in SQL

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

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

FIRST QUESTION: Do I have to create a foreign key for diagnosis on the sick table?

SECOND QUESTION: How should I use ON CASCADE DELETE to ensure that the diagnosis is not cleared 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

asked by anonymous 10.02.2015 / 16:00

2 answers

1

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

FIRST QUESTION: will I have to create a foreign key for diagnosis in   sick table?

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

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

10.02.2015 / 16:32

Error in reference in C # How to pass a Jquery plugin as a parameter to the On event

score 1 · Accepted Answer

If you want a Many-to-Many relationship between Diagnostics and / strong> it is necessary to define another table to represent this relation.

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

Both should be foreign key to ensure that you can not eliminate a patient who has a diagnosis or a diagnosis that has a patient.

ON CASCADE DELETE does not apply to what you intend.