1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| create table Serveur
( ServeurId char(4) not null
, ServeurNom varchar(40) not null
, constraint Serveur_pk primary key (ServeurId)
) ;
create table Switch
( SwitchId char(4) not null
, SwitchNom varchar(40) not null
, constraint Switch_pk primary key (SwitchId)
) ;
create table Config
( ConfigId char(4) not null
, ConfigNom varchar(40) not null
, AdressIP varchar(20) not null
, ServeurId char(4) null
, SwitchId char(4) null
, constraint Config_pk primary key (ConfigId)
, constraint Config_ak unique (AdressIP)
, constraint c_Serveur_fk foreign key (ServeurId) references Serveur
, constraint c_Switch_fk foreign key (SwitchId) references Switch
, CONSTRAINT EXCLUSION
CHECK (ServeurId IS NULL AND SwitchId IS NOT NULL
OR
ServeurId IS NOT NULL AND SwitchId IS NULL)
) ;
insert into Serveur values ('srv1', 'serveur 1') ;
insert into Serveur values ('srv2', 'serveur 2') ;
insert into Switch values ('sw1', 'switch 1') ;
insert into Switch values ('sw2', 'switch 2') ;
insert into Config values ('c1', 'config 1', 'adrIP1', 'srv1', null) ;
insert into Config values ('c2', 'config 2', 'adrIP2', null, 'sw1') ;
insert into Config values ('c3', 'c3', 'adrIP3', 'srv1', 'b1') ;
insert into Config values ('c4', 'c4', 'adrIP4', null, null) ;
Select * from Config |
Partager