Yes, hacking into the catalog is bad. Reason #1 is that if you upgrade to new version and forget to move the hack, things start breaking. Just running pg_dump and loading to the same version on another instance will also lose the hack. There's also always the chance that a new version of Postgres will change so much that your hack is now not possible and force you to go back and re-engineer.
Overriding with your own function is the correct way to go.
The problem is similar to this one: How to have a one-to-many relationship with a privileged child?
The "at most one per group" part of the constraint can be solved with a partial index:
CREATE UNIQUE INDEX is_FavoriteChild
ON x (yid)
WHERE is_principal ;
Another way to solve the problem is by removing the is_principal
column and add a 3rd table. This doesn't solve the "exactly one" either:
CREATE TABLE x (
xid INTEGER NOT NULL PRIMARY KEY,
yid INTEGER NOT NULL REFERENCES y (yid),
UNIQUE (yid, xid)
);
CREATE TABLE x_principal (
xid INTEGER NOT NULL,
yid INTEGER NOT NULL PRIMARY KEY,
FOREIGN KEY (yid, xid) REFERENCES x (yid, xid)
);
If you want to enforce the "exactly one" restriction using DDL alone, it can be done in Postgres with deferrable constraints (I don't think this is an option in SQLite).
For more details and options, you can see the excellent answer in the SO question by @Erwin: Complex foreign key constraint in SQLAlchemy.
(Editing the answer for the additional detail that not all values of y.yid
have to appear in table x
. One table is added):
--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
--- **no other columns**
principal_xid INTEGER NOT NULL
);
CREATE TABLE x (
xid INTEGER NOT NULL PRIMARY KEY,
yid INTEGER NOT NULL REFERENCES y_x (yid)
DEFERRABLE INITIALLY DEFERRED,
UNIQUE (yid, xid)
);
ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
FOREIGN KEY (yid, principal_xid)
REFERENCES x (yid, xid)
DEFERRABLE INITIALLY DEFERRED;
Best Answer
There is no constant for that in PostgreSQL, but if your architecture uses IEEE floating precision values, the maximum should be
1.7976931348623158e308
.Maybe it would be a good idea to use a value that's larger than any reasonable value, but obviously artificial, like
1e300
.