Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table?
Assume the following table that serves as a source for the custom type:
CREATE TABLE source
(
id character varying(20) NOT NULL,
weight smallint NOT NULL,
CONSTRAINT source_pkey PRIMARY KEY (id),
CONSTRAINT source_weight_check CHECK (weight > 0 AND weight < 6)
)
Now, I intend to create the following data table:
CREATE TABLE final
(
name character varying(20) NOT NULL,
data source NOT NULL,
CONSTRAINT final_pkey PRIMARY KEY (name),
)
When inserting records into the final table, the check constraint will not be observed.
Requirements:
- I'd like to avoid creating my own type based of source, because when source changes I'll be forced to remember to also update the custom type.
- I realize that table-based custom types obey domain check constraints. But, on my particular case, the weight field in the source database is ill-suited for a domain. Its check constraints varies from table to table. FYI, it's a value used in calculating weighted averages.