CREATE TABLE automobile (
prov char(2),
numero char (6),
proprietario char(12),
Primary key (prov, numero)
);

CREATE TABLE vigile (
matricola int primary key,
nome char(25) not null,
cognome char(25) not null,
Unique (nome, cognome)
);

CREATE TABLE infrazione (
codice int primary key,
data date not null,
vigile int,
prov char(2),
numero char (6),
Foreign key (prov, numero)
references automobile(prov, numero)
on delete set null on update cascade, 
foreign key (vigile) references vigile(matricola)
on delete set null on update cascade
);

insert into vigile values (343, 'Rossi', 'Luca'), (476, 'Neri', 'Pino'), (548, 'Nicolosia', 'Gino');

insert into automobile values ('MI', '3K9886', 'Nestore'), ('MI', '6D5563', 'Nestore'), ('RM', '7C5567', 'Mancini'), ('RM', '1A6673', 'Mussone'), ('MI', '5E7653', 'Marchi');

insert into infrazione values (65524, '1997-09-03', 343, 'MI', '5E7653'), (87635, '1997-12-04', 476,'MI', '3k9886'), (82236, '1997-12-04', 343, 'RM', '7C5567'), (35632, '1997-12-04', 476, 'RM', '7C5567'), (76543,
'1998-03-05', 548, 'VE', null);

update automobile set prov='TE' where proprietario='Nestore';

select * from automobile;
select * from infrazione;

update vigile set matricola=549 where matricola=548;
select * from infrazione;

CREATE TABLE prova (
codice int primary key,
codice1 int unique
);