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)
);
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?
– ramaral
The goal is that a patient may have several diagnoses and that a diagnosis may also be associated with several patients...
– Mario Medeiros
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.
– pnet
http://magaweb.com.br/blog/incluindo-o-comando-on-delete-cascade-em-uma-tabela/
– pnet