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.
- 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.