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);
[download]

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';
[download]

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();
[download]

---

Fun Fun Fun in the Fluffy Chair

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.