[#CORE-651] Unique Foreign Key - Firebird RDBMS Issue Tracker

Firebird Core

Unique Foreign Key

Created: 01/Jun/04 12:00 AM Updated: 24/Apr/14 04:35 PM

Component/s:

Engine

Affects Version/s:

None

Fix Version/s:

None

SF_ID:

964318

Description

« Hide

SFID: 964318#
Submitted By: ded

Broaden syntax
[ADD] CONSTRAINT [<constraint-identifier>] <constraint-type> <constraint-definition> [USING [ASC[ENDING] | DESC[ENDING]] INDEX <index_name>]

to

[ADD] CONSTRAINT [<constraint-identifier>] <constraint-type> <constraint-definition> [USING [ASC[ENDING] | DESC[ENDING]] [UNIQUE] INDEX <index_name>]

for Foreign Keys. This will allow to don't create Primary Key on one of the tables in 1:1 relations and set limitations on uniquiness of elements in m:n relations without superfluos fully duplicated indices, one for reference integrity, another for uniqueness, and increase performance.

All

Comments

Change History

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Alexander Tyutik added a comment - 19/Apr/07 07:06 AM

I need such feature too, but in 99% cases this FK should be also PK (for one-to-one relations). So maby it's possible to add alternative syntax, such as:

[ADD] CONSTRAINT [<constraint-identifier>]
<constraint-type> <constraint-definition>
[USING INDEX OF {PRIMARY KEY | UNIQUE KEY} <primary_or_unique_constraint_key_name>]

[ Permalink | « Hide ]

Tomas Krejzek added a comment - 17/Jul/08 09:43 AM

Hi, can You tell me the number of version that should have this feature??

Now I have to define 2 indexes on one field to reach this functionality, it is not clear and efficient for inserts

Tom

[ Permalink | « Hide ]

Alex Poloziouk added a comment - 13/May/09 03:36 AM

This is very useful feature indeed.
Why have copy of the same index for Foreign Key if there's Primary Key already?

Alex

[ Permalink | « Hide ]

Carlos de Cumont added a comment - 19/Sep/12 11:36 AM

We also need this feature !
Carlos

[ Permalink | « Hide ]

Emerson Manuel Azevedo added a comment - 04/Feb/13 10:22 PM

We also need this feature !

I'm using a trick is to set the field RDB$UNIQUE_FLAG in RDB$INDICES table, as type 1 (Unique), and found that a backup and restore does not change this flag, so I think you should enable this function because the operation is perfect.
It is a hint to other users who have the same need.

Emerson Manuel Azevedo.