The use of multiple foreign keys on same column in SQL Serverforeign key constraints on same tableChanging...
Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?
How do I create uniquely male characters?
Can divisibility rules for digits be generalized to sum of digits
The use of multiple foreign keys on same column in SQL Server
Why are electrically insulating heatsinks so rare? Is it just cost?
Mage Armor with Defense fighting style (for Adventurers League bladeslinger)
Languages that we cannot (dis)prove to be Context-Free
Maximum likelihood parameters deviate from posterior distributions
What's the output of a record cartridge playing an out-of-speed record
Theorems that impeded progress
What does "Puller Prush Person" mean?
How did the USSR manage to innovate in an environment characterized by government censorship and high bureaucracy?
Smoothness of finite-dimensional functional calculus
"to be prejudice towards/against someone" vs "to be prejudiced against/towards someone"
How could an uplifted falcon's brain work?
How is the claim "I am in New York only if I am in America" the same as "If I am in New York, then I am in America?
Why, historically, did Gödel think CH was false?
What is the offset in a seaplane's hull?
Why Is Death Allowed In the Matrix?
Why do falling prices hurt debtors?
How can I make my BBEG immortal short of making them a Lich or Vampire?
Do I have a twin with permutated remainders?
Why dont electromagnetic waves interact with each other?
Font hinting is lost in Chrome-like browsers (for some languages )
The use of multiple foreign keys on same column in SQL Server
foreign key constraints on same tableChanging field length when foreign keys reference primary key field in tableNeed for indexes on foreign keysComposite primary key from multiple tables / multiple foreign keysForeign Keys to tables where primary key is not clustered indexWhat would I use a MATCH SIMPLE foreign key for?Multiple foreign keys on single columnSQL Server database design with foreign keysComposite Primary Key on partitioned tables, and Foreign KeysIs it best practice to use surrogate keys when creating foreign key constraints in SQL Server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining the same relationship. I want to know what's the use of having multiple foreign keys which are defined on the same column and reference to the same column in another table. What's the benefit of it that SQL Server allows us to do a thing like that?
sql-server foreign-key
asked 12 hours ago
ElGrigElGrig
71416
add a comment |
SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining the same relationship. I want to know what's the use of having multiple foreign keys which are defined on the same column and reference to the same column in another table. What's the benefit of it that SQL Server allows us to do a thing like that?
sql-server foreign-key
asked 12 hours ago
ElGrigElGrig
71416
add a comment |
SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining the same relationship. I want to know what's the use of having multiple foreign keys which are defined on the same column and reference to the same column in another table. What's the benefit of it that SQL Server allows us to do a thing like that?
sql-server foreign-key
asked 12 hours ago
ElGrigElGrig
71416
SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining the same relationship. I want to know what's the use of having multiple foreign keys which are defined on the same column and reference to the same column in another table. What's the benefit of it that SQL Server allows us to do a thing like that?
sql-server foreign-key
sql-server foreign-key
asked 12 hours ago
ElGrigElGrig
71416
asked 12 hours ago
ElGrigElGrig
71416
asked 12 hours ago
ElGrigElGrig
71416
asked 12 hours ago
ElGrigElGrig
71416
asked 12 hours ago
ElGrigElGrig
71416
71416
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns.
It's like having the same check 2 or more times.
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
add a comment |
There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value.
The SQL Server database engine does not stop you from doing so.
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
add a comment |
SQL Server allows you to do a lot of silly things.
You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.
One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.
(As in example below)
CREATE TABLE T1(
T1_Id INT PRIMARY KEY CLUSTERED NOT NULL,
Filler CHAR(4000) NULL,
)
INSERT INTO T1 VALUES (1, '');
CREATE TABLE T2(
T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
Filler CHAR(4000) NULL,
)
ALTER TABLE T1 ADD CONSTRAINT
UQ_T1 UNIQUE NONCLUSTERED(T1_Id)
/*Execution Plan uses clustered index*/
INSERT INTO T2 VALUES (1,1)
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)
ALTER TABLE T2 DROP CONSTRAINT FK
/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)
DROP TABLE T2, T1;
As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
add a comment |
Same reason you can create 50 indexes on the same column, add a second log file, set max server memory to 20MB... most people won't do these things, but there can be legitimate reasons to do them occasionally, so there's no benefit in creating overhead in the engine to add checks against things that are merely ill-advised.
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234086%2fthe-use-of-multiple-foreign-keys-on-same-column-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns.
It's like having the same check 2 or more times.
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
add a comment |
There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns.
It's like having the same check 2 or more times.
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
add a comment |
There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns.
It's like having the same check 2 or more times.
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
There is no use for having identical foreign key constraints., that is on same columns and referencing same table and columns.
It's like having the same check 2 or more times.
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
edited 11 hours ago
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
answered 11 hours ago
ypercubeᵀᴹypercubeᵀᴹ
78.1k11136219
78.1k11136219
add a comment |
add a comment |
There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value.
The SQL Server database engine does not stop you from doing so.
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
add a comment |
There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value.
The SQL Server database engine does not stop you from doing so.
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
add a comment |
There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value.
The SQL Server database engine does not stop you from doing so.
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
There is no benefit to having redundant constraints that differ only by name. Similarly, there is no benefit to having redundant indexes that differ only by name. Both add overhead without value.
The SQL Server database engine does not stop you from doing so.
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
answered 11 hours ago
Dan GuzmanDan Guzman
14.1k21736
14.1k21736
add a comment |
add a comment |
SQL Server allows you to do a lot of silly things.
You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.
One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.
(As in example below)
CREATE TABLE T1(
T1_Id INT PRIMARY KEY CLUSTERED NOT NULL,
Filler CHAR(4000) NULL,
)
INSERT INTO T1 VALUES (1, '');
CREATE TABLE T2(
T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
Filler CHAR(4000) NULL,
)
ALTER TABLE T1 ADD CONSTRAINT
UQ_T1 UNIQUE NONCLUSTERED(T1_Id)
/*Execution Plan uses clustered index*/
INSERT INTO T2 VALUES (1,1)
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)
ALTER TABLE T2 DROP CONSTRAINT FK
/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)
DROP TABLE T2, T1;
As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
add a comment |
SQL Server allows you to do a lot of silly things.
You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.
One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.
(As in example below)
CREATE TABLE T1(
T1_Id INT PRIMARY KEY CLUSTERED NOT NULL,
Filler CHAR(4000) NULL,
)
INSERT INTO T1 VALUES (1, '');
CREATE TABLE T2(
T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
Filler CHAR(4000) NULL,
)
ALTER TABLE T1 ADD CONSTRAINT
UQ_T1 UNIQUE NONCLUSTERED(T1_Id)
/*Execution Plan uses clustered index*/
INSERT INTO T2 VALUES (1,1)
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)
ALTER TABLE T2 DROP CONSTRAINT FK
/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)
DROP TABLE T2, T1;
As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
add a comment |
SQL Server allows you to do a lot of silly things.
You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.
One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.
(As in example below)
CREATE TABLE T1(
T1_Id INT PRIMARY KEY CLUSTERED NOT NULL,
Filler CHAR(4000) NULL,
)
INSERT INTO T1 VALUES (1, '');
CREATE TABLE T2(
T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
Filler CHAR(4000) NULL,
)
ALTER TABLE T1 ADD CONSTRAINT
UQ_T1 UNIQUE NONCLUSTERED(T1_Id)
/*Execution Plan uses clustered index*/
INSERT INTO T2 VALUES (1,1)
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)
ALTER TABLE T2 DROP CONSTRAINT FK
/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)
DROP TABLE T2, T1;
As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
SQL Server allows you to do a lot of silly things.
You can even create a foreign key on a column referencing itself - despite the fact that this can never be violated as every row will meet the constraint on itself.
One edge case where the ability to create two foreign keys on the same relationship would be potentially useful is because the index used for validating foreign keys is determined at creation time. If a better (i.e. narrower) index comes along later then this would allow a new foreign key constraint to be created bound on the better index and then the original constraint dropped without having any gap with no active constraint.
(As in example below)
CREATE TABLE T1(
T1_Id INT PRIMARY KEY CLUSTERED NOT NULL,
Filler CHAR(4000) NULL,
)
INSERT INTO T1 VALUES (1, '');
CREATE TABLE T2(
T2_Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
T1_Id INT NOT NULL CONSTRAINT FK REFERENCES T1 (T1_Id),
Filler CHAR(4000) NULL,
)
ALTER TABLE T1 ADD CONSTRAINT
UQ_T1 UNIQUE NONCLUSTERED(T1_Id)
/*Execution Plan uses clustered index*/
INSERT INTO T2 VALUES (1,1)
ALTER TABLE T2 WITH CHECK ADD CONSTRAINT FK2 FOREIGN KEY(T1_Id)
REFERENCES T1 (T1_Id)
ALTER TABLE T2 DROP CONSTRAINT FK
/*Now Execution Plan now uses non clustered index*/
INSERT INTO T2 VALUES (1,1)
DROP TABLE T2, T1;
As an aside for the interim period whilst both constraints exist any inserts end up being validated against both indexes.
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
edited 6 hours ago
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
answered 7 hours ago
Martin SmithMartin Smith
64.1k10173258
64.1k10173258
add a comment |
add a comment |
Same reason you can create 50 indexes on the same column, add a second log file, set max server memory to 20MB... most people won't do these things, but there can be legitimate reasons to do them occasionally, so there's no benefit in creating overhead in the engine to add checks against things that are merely ill-advised.
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
add a comment |
Same reason you can create 50 indexes on the same column, add a second log file, set max server memory to 20MB... most people won't do these things, but there can be legitimate reasons to do them occasionally, so there's no benefit in creating overhead in the engine to add checks against things that are merely ill-advised.
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
add a comment |
Same reason you can create 50 indexes on the same column, add a second log file, set max server memory to 20MB... most people won't do these things, but there can be legitimate reasons to do them occasionally, so there's no benefit in creating overhead in the engine to add checks against things that are merely ill-advised.
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
Same reason you can create 50 indexes on the same column, add a second log file, set max server memory to 20MB... most people won't do these things, but there can be legitimate reasons to do them occasionally, so there's no benefit in creating overhead in the engine to add checks against things that are merely ill-advised.
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
answered 10 hours ago
Aaron Bertrand♦Aaron Bertrand
154k18298493
154k18298493
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234086%2fthe-use-of-multiple-foreign-keys-on-same-column-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
