fix foreign key constraints

1 files changed, 75 insertions, 33 deletions

diff --git a/server/sql/init.sql b/server/sql/init.sql
index c0c2f96..72bf716 100644
--- a/server/sql/init.sql
+++ b/server/sql/init.sql
@@ -11,7 +11,7 @@ create table `hosts` (
 
 drop table if exists `env`;
 create table `env` (
-  `uuid` binary (16) references hosts.uuid,
+  `uuid` binary (16),
   `platform` varchar (128),
   `arch` varchar (16),
   `chost` varchar (32),
@@ -20,10 +20,12 @@ create table `env` (
   `fflags` varchar (64),
   `ldflags` varchar (64),
   `makeopts` varchar (8),
-  `lastsync` timestamp,
+  `lastsync` timestamp null default null,
   `profile` varchar (64),
   `sync` varchar (128),
-  primary key (`uuid`)
+  primary key (`uuid`),
+  foreign key (`uuid`) references `hosts`(`uuid`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `keywords`;
@@ -35,9 +37,13 @@ create table `keywords` (
 
 drop table if exists `global_keywords`;
 create table `global_keywords` (
-  `uuid` binary (16) references hosts.uuid,
-  `kwkey` bigint unsigned references keywords.kwkey,
-  primary key (`uuid`, `kwkey`)
+  `uuid` binary (16),
+  `kwkey` bigint unsigned,
+  primary key (`uuid`, `kwkey`),
+  foreign key (`uuid`) references `hosts`(`uuid`)
+  on delete cascade on update cascade,
+  foreign key (`kwkey`) references `keywords`(`kwkey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `lang`;
@@ -49,9 +55,13 @@ create table `lang` (
 
 drop table if exists `host_lang`;
 create table `host_lang` (
-  `uuid` binary (16) references hosts.uuid,
-  `lkey` bigint unsigned references lang.lkey,
-  primary key (`uuid`, `lkey`)
+  `uuid` binary (16),
+  `lkey` bigint unsigned,
+  primary key (`uuid`, `lkey`),
+  foreign key (`uuid`) references `hosts`(`uuid`)
+  on delete cascade on update cascade,
+  foreign key (`lkey`) references `lang`(`lkey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `features`;
@@ -63,9 +73,13 @@ create table `features` (
 
 drop table if exists `host_features`;
 create table `host_features` (
-  `uuid` binary (16) references hosts.uuid,
-  `fkey` bigint unsigned references features.fkey,
-  primary key (`uuid`, `fkey`)
+  `uuid` binary (16),
+  `fkey` bigint unsigned,
+  primary key (`uuid`, `fkey`),
+  foreign key (`uuid`) references `hosts`(`uuid`)
+  on delete cascade on update cascade,
+  foreign key (`fkey`) references `features`(`fkey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `gentoo_mirrors`;
@@ -77,9 +91,13 @@ create table `gentoo_mirrors` (
 
 drop table if exists `host_mirrors`;
 create table `host_mirrors` (
-  `uuid` binary (16) references hosts.uuid,
-  `mkey` bigint unsigned references gentoo_mirrors.mkey,
-  primary key (`uuid`, `mkey`)
+  `uuid` binary (16),
+  `mkey` bigint unsigned,
+  primary key (`uuid`, `mkey`),
+  foreign key (`uuid`) references `hosts`(`uuid`)
+  on delete cascade on update cascade,
+  foreign key (`mkey`) references `gentoo_mirrors`(`mkey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `packages`;
@@ -101,14 +119,22 @@ create table `repositories` (
 drop table if exists `installed_packages`;
 create table `installed_packages` (
   `ipkey` serial,
-  `uuid` binary (16) references hosts.uuid,
-  `pkey` bigint unsigned references packages.pkey,
-  `build_time` timestamp,
+  `uuid` binary (16),
+  `pkey` bigint unsigned,
+  `build_time` timestamp null default null,
   `counter` bigint unsigned,
-  `kwkey` bigint unsigned references keywords.kwkey,
-  `rkey` varchar (64) references repositories.rkey,
+  `kwkey` bigint unsigned,
+  `rkey` varchar (64),
   `size` bigint unsigned,
-  primary key (`uuid`, `pkey`)
+  primary key (`uuid`, `pkey`),
+  foreign key (`uuid`) references `hosts`(`uuid`)
+  on delete cascade on update cascade,
+  foreign key (`pkey`) references `packages`(`pkey`)
+  on delete cascade on update cascade,
+  foreign key (`kwkey`) references `keywords`(`kwkey`)
+  on delete cascade on update cascade,
+  foreign key (`rkey`) references `repositories`(`rkey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `useflags`;
@@ -120,28 +146,44 @@ create table `useflags` (
 
 drop table if exists `global_useflags`;
 create table `global_useflags` (
-  `uuid` binary (16) references hosts.uuid,
-  `ukey` bigint unsigned references useflags.ukey,
-  primary key (`uuid`, `ukey`)
+  `uuid` binary (16),
+  `ukey` bigint unsigned,
+  primary key (`uuid`, `ukey`),
+  foreign key (`uuid`) references `hosts`(`uuid`)
+  on delete cascade on update cascade,
+  foreign key (`ukey`) references `useflags`(`ukey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `plus_useflags`;
 create table `plus_useflags` (
-  `ipkey` bigint unsigned references installed_packages.ipkey,
-  `ukey` bigint unsigned references useflags.ukey,
-  primary key (`ipkey`, `ukey`)
+  `ipkey` bigint unsigned,
+  `ukey` bigint unsigned,
+  primary key (`ipkey`, `ukey`),
+  foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+  on delete cascade on update cascade,
+  foreign key (`ukey`) references `useflags`(`ukey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `minus_useflags`;
 create table `minus_useflags` (
-  `ipkey` bigint unsigned references installed_packages.ipkey,
-  `ukey` bigint unsigned references useflags.ukey,
-  primary key (`ipkey`, `ukey`)
+  `ipkey` bigint unsigned,
+  `ukey` bigint unsigned,
+  primary key (`ipkey`, `ukey`),
+  foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+  on delete cascade on update cascade,
+  foreign key (`ukey`) references `useflags`(`ukey`)
+  on delete cascade on update cascade
 );
 
 drop table if exists `unset_useflags`;
 create table `unset_useflags` (
-  `ipkey` bigint unsigned references installed_packages.ipkey,
-  `ukey` bigint unsigned references useflags.ukey,
-  primary key (`ipkey`, `ukey`)
+  `ipkey` bigint unsigned,
+  `ukey` bigint unsigned,
+  primary key (`ipkey`, `ukey`),
+  foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+  on delete cascade on update cascade,
+  foreign key (`ukey`) references `useflags`(`ukey`)
+  on delete cascade on update cascade
 );