Issue Details (XML | Word | Printable)

Key: CORE-651
Type: New Feature New Feature
Status: Open Open
Priority: Minor Minor
Assignee: Alexander Peshkov
Reporter: ded
Votes: 15
Watchers: 5

If you were logged in you would be able to see more operations.
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>]


  [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: Ascending order - Click to sort in descending order
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>]

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


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?


Carlos de Cumont added a comment - 19/Sep/12 11:36 AM
We also need this feature !

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.