Re: Custom Data Type Question

On Mon, 2006-11-20 at 16:30 -0500, Andrew Dunstan wrote:

> > IMHO we need to optimise FOREIGN KEY checks. One way of doing that is by
> > having enums that build the allowable values into the datatypes, I can
> > think of others. An overall evaluation of the various approaches should
> > be made before we settle on a specific one.
> >
>
> Using the submitted patch, FK checks should be VERY fast - the stored
> values are just oids.

I see that they would be.

> > My requirements list would be to allow FOREIGN KEYS to
> > - be specified in SQL standard syntax
> > - work as fast as CHECK (col IN (1,2,3))
> >
>
> If I understand it, you are really not talking about doing an FK check
> on an enum value, but rather using an FK check as a means of validating
> an enum. That is not what we are talking about. But the validity checks
> will be at least as fast as any check constraint.

Not really talking about enums at all. Just trying to understand how to
optimize the general case of Foreign Keys, which I have suddenly
realised is exactly what you are trying to achieve with enums.

> Boom goes
> data integrity.

My point exactly (assuming I didn't quote you out of context).

> I should add that it would have been nice if this discussion had
> happened back in August when the work was being done.

I'm happy to apologise. I hadn't made the connection at that point
between the role of an enum datatype and the need to optimise FKs.

Since MySQL has an enum type, it will aid portability to allow an enum
type. However, I'm interested in providing a mechanism to speed up FKs
without the *need* to adopt specific data domain specific datatypes.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com