Thread: Is CREATE TYPE an alias for CREATE DOMAIN?
Hi Could somebody please tell me if CREATE TYPE is equivalent to CREATE DOMAIN? If not is there a work around --- Regards John Dean, co-author of Rekall, the only alternative to MS Access
John Dean wrote: > Hi > > Could somebody please tell me if CREATE TYPE is equivalent to CREATE > DOMAIN? If not is there a work around What do you mean by "equivalent"? You wouldn't use them in the same way, and I'm not sure what a work-around would consist of. What are you trying to do? -- Richard Huxton Archonet Ltd
On Thu, Dec 22, 2005 at 09:36:52AM +0000, Richard Huxton wrote: > John Dean wrote: > >Hi > > > >Could somebody please tell me if CREATE TYPE is equivalent to CREATE > >DOMAIN? If not is there a work around > > What do you mean by "equivalent"? You wouldn't use them in the same way, > and I'm not sure what a work-around would consist of. > > What are you trying to do? Some (most?) database's idea of 'creating a type' is actually what we consider creating a domain, since many databases don't support users adding arbitrary types to the system. I suspect this user is trying to port some code over... -- 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
Jim C. Nasby wrote: > On Thu, Dec 22, 2005 at 09:36:52AM +0000, Richard Huxton wrote: > > John Dean wrote: > > >Hi > > > > > >Could somebody please tell me if CREATE TYPE is equivalent to CREATE > > >DOMAIN? If not is there a work around > > > > What do you mean by "equivalent"? You wouldn't use them in the same way, > > and I'm not sure what a work-around would consist of. > > > > What are you trying to do? > > Some (most?) database's idea of 'creating a type' is actually what we > consider creating a domain, since many databases don't support users > adding arbitrary types to the system. I suspect this user is trying to > port some code over... CREATE DOMAIN builds on an existing data type and adds additional characteristics and checks to the type. It is sort of like a macro for types. CREATE TYPE creates a new data type, independent of existing data types, and usually requires C code and a shared object file to load into the database. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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? -- Peter Eisentraut http://developer.postgresql.org/~petere/
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. -- 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
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