diff options
| author | Yorhel <git@yorhel.nl> | 2016-10-06 09:52:18 +0200 |
|---|---|---|
| committer | Yorhel <git@yorhel.nl> | 2016-10-06 09:52:20 +0200 |
| commit | d2d48920f441f6c0ab633cf9aa5ed4a8dd8475f1 (patch) | |
| tree | 1eb7c104b83d57e42828f58b8f6152ca499bb4a6 /sql | |
| parent | 488b88c3cf1ccce90a05144dad4ea4e7601f57e8 (diff) | |
SQL: Add ON DELETE clause to foreign keys
This makes it easier to remove a complete package or system when
debugging.
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/schema.sql | 6 | ||||
| -rw-r--r-- | sql/update-2016-10-06.sql | 3 |
2 files changed, 6 insertions, 3 deletions
diff --git a/sql/schema.sql b/sql/schema.sql index f597797..ff264a8 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -13,7 +13,7 @@ CREATE TABLE contents ( CREATE TABLE packages ( id SERIAL PRIMARY KEY, - system integer NOT NULL REFERENCES systems(id), + system integer NOT NULL REFERENCES systems(id) ON DELETE CASCADE, category varchar, name varchar NOT NULL, UNIQUE(system, name, category) -- Note the order, lookups on (system,name) are common @@ -21,14 +21,14 @@ CREATE TABLE packages ( CREATE TABLE package_versions ( id SERIAL PRIMARY KEY, - package integer NOT NULL REFERENCES packages(id), + package integer NOT NULL REFERENCES packages(id) ON DELETE CASCADE, version varchar NOT NULL, released date NOT NULL, UNIQUE(package, version) ); CREATE TABLE man ( - package integer NOT NULL REFERENCES package_versions(id), + package integer NOT NULL REFERENCES package_versions(id) ON DELETE CASCADE, name varchar NOT NULL, section varchar NOT NULL, filename varchar NOT NULL, diff --git a/sql/update-2016-10-06.sql b/sql/update-2016-10-06.sql new file mode 100644 index 0000000..ff4b394 --- /dev/null +++ b/sql/update-2016-10-06.sql @@ -0,0 +1,3 @@ +ALTER TABLE packages DROP CONSTRAINT packages_system_fkey, ADD CONSTRAINT packages_system_fkey FOREIGN KEY(system) REFERENCES systems(id) ON DELETE CASCADE; +ALTER TABLE package_versions DROP CONSTRAINT package_versions_package_fkey, ADD CONSTRAINT package_versions_package_fkey FOREIGN KEY(package) REFERENCES packages(id) ON DELETE CASCADE; +ALTER TABLE man DROP CONSTRAINT man_package_fkey, ADD CONSTRAINT man_package_fkey FOREIGN KEY(package) REFERENCES package_versions(id) ON DELETE CASCADE; |