Disclaimer
This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.
For all I know, there are only two differences between a PRIMARY KEY
and a UNIQUE
constraint in the catalog tables (the index itself is identical):
pg_index.indisprimary
:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE
pg_constraint.contype
:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'
You could convert constraint and index in place, from PRIMARY KEY
constraint to UNIQUE
constraint, my_idx
being the (optionally schema-qualified) index name:
UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;
Or upgrade from UNIQUE
to PRIMARY KEY
:
UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;
Original answer is wrong! See Edit 1 for the corrected version.
Original answer
An amazing solution would require a foreign key to a column in a view or a inherited table, but unfortunately PostgreSQL (I suppose that's your RDBMS because of the tag) does not have that (yet).
I think a simple change in the way you organize the data would suffice: create a table like ItemsAvailableQuantity, connecting an Item with its availability which will be references in the orders. When an item is not available anymore, DELETE
it from it.
CREATE TABLE Item (
id serial NOT NULL
, name text NOT NULL
, cost numeric
, PRIMARY KEY (id)
, CONSTRAINT positive_cost
CHECK (cost > 0)
);
CREATE TABLE ItemAvailableQuantity (
id serial NOT NULL
, item_id integer NOT NULL
, quantity integer NOT NULL
, PRIMARY KEY (id)
, FOREIGN KEY (item_id)
REFERENCES Item (id)
ON UPDATE CASCADE
ON DELETE CASCADE
, CONSTRAINT postive_quantity -- This constraint is the same as
CHECK (quantity > 0) -- checking something like `available = TRUE`.
);
CREATE TABLE ItemOrder ( -- Changed the name from `Order` because
id serial NOT NULL -- PostgreSQL refuses that name, somehow
, bill_id integer NOT NULL
, item_id integer NOT NULL
, units integer NOT NULL
, PRIMARY KEY (id)
, FOREIGN KEY (item_id)
REFERENCES ItemAvailableQuantity (id)
ON UPDATE CASCADE
ON DELETE CASCADE
-- Uncomment when `Bill` table is ready
-- , FOREIGN KEY (bill_id)
-- REFERENCES Bill (id)
-- ON UPDATE CASCADE
-- ON DELETE CASCADE
, CONSTRAINT positive_units
CHECK (units > 0)
);
Notice! The constraint positive_units may cause problems when your software reduces the units and reaches 0. Make it something like CHECK >= 0
if needed, or add a trigger that automatically DELETE
-s rows when units reaches 0 (or less) on each INSERT
or UPDATE
. This would preserve the table ItemAvailableQuantity to have only actually available items, which is what we want for being referenced from the table ItemOrder.
This should solve your problem. It's not an exact answer to your question. That would involve a trigger or a CHECK
calling a function as in the link you provided.
To easily see the quantity of the items then, just create a view that joins ItemAvailableQuantity and Item. If you really want then, make it INSERT
-able with a trigger (see yellow-box warning).
Edit 1
Actually Order (a.k.a. ItemOrder) should reference the Item instead of ItemAvailableQuantity to avoid any problem when the Item is not currently available, as stated in the comment.
This suggest we should remove the whole table ItemAvailableQuantity and only add a column available_quantity on Item.
CREATE TABLE Item (
id serial NOT NULL
, name text NOT NULL
, cost numeric
, available_quantity integer NOT NULL
, PRIMARY KEY (id)
, CONSTRAINT positive_cost
CHECK (cost > 0)
, CONSTRAINT non_negative_quantity
CHECK (quantity >= 0)
);
Then, to be certain of inserting only available items into orders we could just run
INSERT INTO ItemOrder (bill_id, item_id, units) VALUES
(SELECT id FROM Bill WHERE condition = something -- customize at will
, SELECT id FROM Item WHERE available_quantity >= wanted_quantity
AND other_condition = something
, wanted_quantity)
;
where wanted_quantity
is a parameter passed by your software to the query.
Still, solves the problem, but is no direct answer to the question.
Best Answer
pg_constraint.conkey
is an array column, so we join ona.attnum = ANY(c.conkey)
and the oid of the relation additionally. Like:But I am doubtful of your overall objective:
CHECK
constraints are always table constraints in Postgres and can reference any number of table columns. Even when formulated as column constraints! The manual:So
some_col
andsome_col_also
can be referenced at the same time (multiple times). And both can be in the constraint name. I don't see how a completely dynamic solution can be bullet-proof, unless you can rely on a strict naming convention. (Can you really?). Is the following a reference tosome_col
orsome_col_also
?Rewriting the constraint expression itself also demands attention.