Search Postgresql Archives

Re: Is CREATE TYPE an alias for CREATE DOMAIN?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux