Re: naming conventions for constraints

On 5 May 2003 22:09:42 GMT, Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote:

[snip]
>> than anything else. And, IME, users have less trouble with multiple
>> four-digit numbers than with long numbers. (They communicate
>> 1234-5678-9012 more easily and more reliably than 123456789012.)

I should have added that the users we tested communicated 1234-5678-9012 more reliably than three-digit groups like 123-456-789-012, too. That surprised me. Frankly, I've never worked on a system that needed 900 billion constraints. Or 900 thousand. But hope springs eternal . . .

>Then again
>
> ORA-02291: integrity constraint (RENE.FK_CUSTOMER) violated - parent key not found
>
>can be more easily transmitted and communicated as
>
> ORA-02291: integrity constraint (RENE.FK_1234_5678_9012) violated - parent key not found
>
>and does not need to be mapped in order to be mapped to which table it refers.

If those two choices exhausted the possibilities, I'd prefer FK_CUSTOMER. But they don't exhaust the possibilities, and they don't scale, and that's the problem.

A complex schema might have dozens of foreign key references to a table of customers; some of them might even be foreign key references to an alternate key (a candidate key) column or columns, not to the primary key. And there will probably be other constraints that refer to the table of customers, too. The other constraints are liable to be scattered all over hell and half of Georgia.

That's why our convention requires names like this unabbreviated one.

 Orders_CustomerCode_Domain

It identifies a constraint on the data element CustomerCode in the entity Orders. The constraint establishes the domain (in the logical sense, not in the SQL sense). It maps to a foreign key reference to a table of customers, but in other cases a domain constraint could be a CHECK constraint. (For example, to check that sex is in 'M', 'F', '?'.)

In extreme cases, such as a Lotus Notes database, "Orders" might map to a collection of documents (in the sense that Lotus Notes defines "document"); "CustomerCode" might map to a field; and "Domain" might map to a Notes @DBLookup() function or to an agent.

And maybe that's the crux. I don't have the luxury of working only in Oracle. I don't even have the luxury of working only in SQL databases.

Heck, I don't even have the luxury of working only in databases.

-- 
Mike Sherrill
Information Management Systems