If you are using PostgreSQL then there's an explicit syntax to enable this sort of thing. PostgreSQL will then allow you to query on the assets table and see all the "objects" that are Assets. If you want to see just the DocumentAsset objects then use document_asset table. So far this schema by default won't create inconsistant Asset objects &em; the asset_id is controlled by a single sequence so if that's enough assurance you can proceed and know that asset_id is unique without explicitly constraining it.
CREATE SEQUENCE asset_seq;
CREATE TABLE assets (
asset_id integer not null default nextval('asset_seq'),
desk text not null,
status text not null,
priority text not null,
deadline date not null
);
CREATE TABLE documentasset (
doc_id integer not null unique,
label text not null
) INHERITS (assets);
CREATE TABLE mediaasset (
media_id integer not null unique
) INHERITS (assets);
If you really have to ensure that asset_id is unique then you have to step outside of SQL. PostgreSQL has these nice inheritance features but doesn't yet allow you to constrain them declaratively. If you add on this PL/pgSQL code then it will constrain the inheritance for you. All of thsi is obviously just a start but it should get you thinking of the possibilities.
/* From your command prompt type
createlang plpgsql <database name>
It generates the following code automatically so it
isn't nessessary to remember this next bit
*/
CREATE FUNCTION "plpgsql_call_handler" ()
RETURNS opaque AS '/usr/local/lib/plpgsql.so',
'plpgsql_call_handler' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER "plpgsql_call_handler"
LANCOMPILER 'PL/pgSQL';
And now to create a PL/pgSQL function and hook it into the right place. Please be aware I just wrote this into a window and didn't try to run it first or even verify that it's syntactically correct. You'll want to read up on PostgreSQL's triggers and look into either implementing this sort of function in PL/pgSQL or maybe it's TCL or Perl implementations.
CREATE FUNCTION asset_id_ins_upd () RETURNS OPAQUE AS '
DECLARE
found INTEGER;
BEGIN
IF TG_OP = ''UPDATE'' THEN
-- Return OK for unchanging asset_id on
-- update operations
IF NEW.asset_id = OLD.asset_id THEN
RETURN NEW;
END IF;
END IF;
SELECT 1 INTO found
FROM assets
WHERE assets.asset_id = NEW.asset_id;
IF FOUND THEN
RAISE EXCEPTION
END IF;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
CREATE TRIGGER assets_asset_id_ins_upd BEFORE INSERT OR UPDATE ON ass
+ets
FOR EACH ROW EXECUTE PROCEDURE asset_id_ins_upd();
Fun Fun Fun in the Fluffy Chair |