0
I’m trying to leave the value default
'SEM CURSO'
in the student table when the course is deleted, but the way I am doing the value of the foreign key is not getting default and yes NULL
. I am doing the script incorrectly or Postgresql does not support ON DELETE SET DEFAULT
?
The strange thing is that even though Postgresql does not support, the script runs normally.
Follows:
CREATE TABLE curso (
nome varchar(30) primary key default 'SEM CURSO'
);
CREATE TABLE aluno (
nro_matric int primary key,
nome varchar(50),
curso_id varchar(30),
foreign key (curso_id) references curso(nome) on delete set default on update cascade
);
insert into curso(nome) values ('Sistemas de Informação');
insert into curso(nome) values ('Ciencia da Computação');
insert into aluno(nro_matric, nome, curso_id) values (201701, 'Marcio Alves', 'Sistemas de Informação');
insert into aluno(nro_matric, nome, curso_id) values (201702, 'Caio Ribeiro', 'Sistemas de Informação');
insert into aluno(nro_matric, nome, curso_id) values (201703, 'Leticia Alves', 'Ciencia da Computação');
insert into aluno(nro_matric, nome, curso_id) values (201704, 'Fabio Antonio', 'Sistemas de Informação');
delete from curso where nome = 'Ciencia da Computação';
select * from aluno;
Of the manual:
SET DEFAULT

 Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.)

– anonimo
I tested with not null but it didn’t work tb, I wonder how the correct script would be
– Ygor Salles Aniceto Carvalho
You need to create a record in the table
curso
with the countrysidenome
= 'NO COURSE' or allow the fieldcurso_id
tablealuno
stay with NULL when not associated with course.– anonimo