Constraints

In the Ingres schema the only column constraints used in “tecdb” and “infdb” are “not null” and “not null not default”. Most columns are nullable, the exception is primary keys which are “not null not default” (must have a non null value on insert) and non-obligatory foreign keys. PostgreSQL supports the “not null” constraint so no explicit mapping is required. There is no equivalent in PostgreSQL of “not default” but since it is always combined with “not null” then a value (other than null) must be provided on entry anyway so its superfluous so the mapping will be to drop it (in PostgreSQL “not default” is the column constraint default unless an explicit DEFAULT clause is provided).

Uniqueness constraints are done in Ingres by modifying the internal table structure to BTREE or HASH unique on one or more columns. Additional BTREE/HASH indexes may be defined for performance. Tables are also all defined with “no duplicates”, possibly unnecessarily in many cases (probably done to make an earlier incantation of the user interface guaranteed to work). Generally uniqueness constraint columns parallel primary key columns however this is not always the case. PostgreSQL does not have table structure modifications like Ingres, instead the CREATE INDEX syntax is used to create BTREE or HASH indexes. PostgreSQL does not have a direct equivalent to “no duplicates” however you could use a UNIQUE clause including all columns to the same effect. We won’t map “no duplicates”. We can use dictionary information to replace uniqueness constraints defined as explicit table structure modifications with PRIMARY KEY or UNIQUE clauses as appropriate. However, there will be some cases that will need to be manually mapped.