Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unique Foreign Key [CORE651] #1017

Open
firebird-automations opened this issue Jun 1, 2004 · 9 comments
Open

Unique Foreign Key [CORE651] #1017

firebird-automations opened this issue Jun 1, 2004 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: ded (ded)

Votes: 15

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Alice F. Bird (firebirds)

description: 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 Foreing 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 uniquiness,
and increase performance.

=>

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 Foreing 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 uniquiness,
and increase performance.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Major [ 3 ] => Minor [ 4 ]

Component: Engine [ 10000 ]

SF_ID: 964318 =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

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>]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10675 ] => Firebird [ 15041 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Tomas Krejzek (respektive)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Alex Poloziouk (apoloziouk)

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

Alex

@firebird-automations
Copy link
Collaborator Author

Commented by: Carlos de Cumont (cdecu)

We also need this feature !
Carlos

@firebird-automations
Copy link
Collaborator Author

Commented by: Emerson Manuel Azevedo (emerson_azevedo)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: 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 Foreing 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 uniquiness,
and increase performance.

=>

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants