Re: Is CREATE TYPE an alias for CREATE DOMAIN?

Hi Jim

I have checked through the docs for:-
1. Interbase/Firebird
2. Sybase/MS SQL Server
3. Oracle
4. DB2
5. PostgreSQL
BTW I didn't bother to check the MySQL docs because I do not consider MySQL
to be a RDBMS

It seems that only Interbase/Firebird and PostgreSQL supports the CREATE
DOMAIN syntax. DB2 includes something similar - CREATE DISTINCTIVE TYPE.
But it doesn't allow a constraint to be included

At 17:09 28/12/2005, you wrote:
>On Thu, Dec 22, 2005 at 05:16:16PM +0100, Peter Eisentraut wrote:
> > Jim C. Nasby wrote:
> > > Some (most?) database's idea of 'creating a type' is actually what we
> > > consider creating a domain,
> >
> > Which databases do such a thing?
>
>IIRC, Oracle, DB2, Sybase and MSSQL, though my memory's rusty... and I
>should have mentioned that most are just creating an alias for a type
>name, so you can't add stuff like constraints to the new type.

Those RDBMS which do support the CREATE DOMAIN syntax allows the inclusion
of a named constraint and/or a CHECK constrain
Below is a copy of the first few lines from the PostgreSQL SQL Language
Reference


CREATE DOMAIN




Name

CREATE DOMAIN -- define a new domain


Synopsis


CREATE DOMAIN name [AS] data_type
     [ DEFAULT expression ]
     [ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }


Description

CREATE DOMAIN creates a new data domain. The user who defines a domain
becomes its owner.

If a schema name is given (for example, CREATE DOMAIN myschema.mydomain
...) then the domain is created in the specified schema. Otherwise it is
created in the current schema. The domain name must be unique among the
types and domains existing in its schema.

Domains are useful for abstracting common fields between tables into a
single location for maintenance. For example, an email address column may
be used in several tables, all with the same properties. Define a domain
and use that rather than setting up each table's constraints individually.

Sybase/MS SQL Server makes use of the store procedure - sp_addtype, which
is similar to DB2's CREATE DISTINCTIVE TYPE
Oracle uses a variation on the CREATE TYPE syntax. But just like Sybase, MS
SQL Server and DB2 it does not accept a named constraint or CHECK clause


>--
>Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
>Pervasive Software      http://pervasive.com    work: 512-231-6117
>vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

---

Regards
John Dean,
co-author of Rekall,
the only alternative
to MS Access