Procedure for Creating a Subtype
Before you create a named ROW type as a subtype in an inheritance
hierarchy, check the following information:
- Verify that you are authorized to create new data types. You must have the Resource privilege on the database. You can find this information in the sysusers system catalog table.
- Verify that the supertype exists. You can find this information in the sysxtdtypes system catalog table.
- Verify that you are authorized to create subtypes to that supertype. You must have the Under privilege on the supertype. You can find this information in the sysusers system catalog table.
- Verify that the name that you declare for the named ROW type is unique. In an ANSI-compliant database, the owner.type combination must be unique within the database. In a database that is not ANSI-compliant, the name must be unique among data type names in the database. To verify whether the name for a new data type is unique, check the sysxtdtypes system catalog table. The name must not be the name of an existing data type.
- If you are defining fields for the ROW type, check that no duplicate field names exist in both new and inherited fields.
Important: When you create a
subtype, you cannot redefine fields that it inherited for its supertype.
If you attempt to redefine these fields, the database server returns
an error.
You cannot apply constraints to named ROW data types, but you can specify constraints when you create or alter a table that uses the named ROW types. You can also specify NOT NULL constraints on individual fields of a ROW type.