Why is IDENTITY UNIQUE even allowed in SQL Server?
Why is this
CREATE TABLE ORDERS
(
OrderID INT IDENTITY(1, 1) UNIQUE,
BuyerID INT,
PRIMARY KEY(OrderID, BuyerID)
);
even allowed in SQL Server?
I mean, why is IDENTITY(1, 1) UNIQUE allowed?
Doesn't IDENTITY already mean that the values are gonna be unique?
It's like static const in C# - const is already static (static const isn't allowed in C#)
sql-server edited Nov 12 at 19:38
marc_s
570k12811021250
asked Nov 12 at 19:36
Марк Павлович
255
add a comment |
Why is this
CREATE TABLE ORDERS
(
OrderID INT IDENTITY(1, 1) UNIQUE,
BuyerID INT,
PRIMARY KEY(OrderID, BuyerID)
);
even allowed in SQL Server?
I mean, why is IDENTITY(1, 1) UNIQUE allowed?
Doesn't IDENTITY already mean that the values are gonna be unique?
It's like static const in C# - const is already static (static const isn't allowed in C#)
sql-server edited Nov 12 at 19:38
marc_s
570k12811021250
asked Nov 12 at 19:36
Марк Павлович
255
it's just another constraint. Identity can be disabled for inserts, allowing you to give it explicit values....hence, you can have duplicates. It's just not the same
– Lamak
Nov 12 at 19:38
NO!IDENTITYalone does NOT say anything about uniqueness! If you fiddle with the identity values (like usingDBCC RESEEDand such), there's nothing in theIDENTITYcolumn code that stops SQL Server from issuing a value that's already been assigned before. .....
– marc_s
Nov 12 at 19:39
What is disallowed is specifyingUNIQUE PRIMARY KEY. There's no technical reason preventing a primary key constraint and a unique constraint from both being defined on a column, but SQL Server will nevertheless disallow it because there's no point. Duplicate indexes are otherwise fine, even if they're exactly the same, and you can even put a unique index on a primary key column if you're so inclined. This is more in line with yourstatic constexample.
– Jeroen Mostert
Nov 12 at 20:03
add a comment |
Why is this
CREATE TABLE ORDERS
(
OrderID INT IDENTITY(1, 1) UNIQUE,
BuyerID INT,
PRIMARY KEY(OrderID, BuyerID)
);
even allowed in SQL Server?
I mean, why is IDENTITY(1, 1) UNIQUE allowed?
Doesn't IDENTITY already mean that the values are gonna be unique?
It's like static const in C# - const is already static (static const isn't allowed in C#)
sql-server edited Nov 12 at 19:38
marc_s
570k12811021250
asked Nov 12 at 19:36
Марк Павлович
255
Why is this
CREATE TABLE ORDERS
(
OrderID INT IDENTITY(1, 1) UNIQUE,
BuyerID INT,
PRIMARY KEY(OrderID, BuyerID)
);
even allowed in SQL Server?
I mean, why is IDENTITY(1, 1) UNIQUE allowed?
Doesn't IDENTITY already mean that the values are gonna be unique?
It's like static const in C# - const is already static (static const isn't allowed in C#)
sql-server sql-server edited Nov 12 at 19:38
marc_s
570k12811021250
asked Nov 12 at 19:36
Марк Павлович
255
edited Nov 12 at 19:38
marc_s
570k12811021250
asked Nov 12 at 19:36
Марк Павлович
255
edited Nov 12 at 19:38
marc_s
570k12811021250
edited Nov 12 at 19:38
marc_s
570k12811021250
edited Nov 12 at 19:38
marc_s
570k12811021250
570k12811021250
asked Nov 12 at 19:36
Марк Павлович
255
asked Nov 12 at 19:36
Марк Павлович
255
asked Nov 12 at 19:36
Марк Павлович
255
255
it's just another constraint. Identity can be disabled for inserts, allowing you to give it explicit values....hence, you can have duplicates. It's just not the same
– Lamak
Nov 12 at 19:38
NO!IDENTITYalone does NOT say anything about uniqueness! If you fiddle with the identity values (like usingDBCC RESEEDand such), there's nothing in theIDENTITYcolumn code that stops SQL Server from issuing a value that's already been assigned before. .....
– marc_s
Nov 12 at 19:39
What is disallowed is specifyingUNIQUE PRIMARY KEY. There's no technical reason preventing a primary key constraint and a unique constraint from both being defined on a column, but SQL Server will nevertheless disallow it because there's no point. Duplicate indexes are otherwise fine, even if they're exactly the same, and you can even put a unique index on a primary key column if you're so inclined. This is more in line with yourstatic constexample.
– Jeroen Mostert
Nov 12 at 20:03
add a comment |
it's just another constraint. Identity can be disabled for inserts, allowing you to give it explicit values....hence, you can have duplicates. It's just not the same
– Lamak
Nov 12 at 19:38
NO!IDENTITYalone does NOT say anything about uniqueness! If you fiddle with the identity values (like usingDBCC RESEEDand such), there's nothing in theIDENTITYcolumn code that stops SQL Server from issuing a value that's already been assigned before. .....
– marc_s
Nov 12 at 19:39
What is disallowed is specifyingUNIQUE PRIMARY KEY. There's no technical reason preventing a primary key constraint and a unique constraint from both being defined on a column, but SQL Server will nevertheless disallow it because there's no point. Duplicate indexes are otherwise fine, even if they're exactly the same, and you can even put a unique index on a primary key column if you're so inclined. This is more in line with yourstatic constexample.
– Jeroen Mostert
Nov 12 at 20:03
it's just another constraint. Identity can be disabled for inserts, allowing you to give it explicit values....hence, you can have duplicates. It's just not the same
– Lamak
Nov 12 at 19:38
it's just another constraint. Identity can be disabled for inserts, allowing you to give it explicit values....hence, you can have duplicates. It's just not the same
– Lamak
Nov 12 at 19:38
NO!
IDENTITY alone does NOT say anything about uniqueness! If you fiddle with the identity values (like using DBCC RESEED and such), there's nothing in the IDENTITY column code that stops SQL Server from issuing a value that's already been assigned before. .....– marc_s
Nov 12 at 19:39
NO!
IDENTITY alone does NOT say anything about uniqueness! If you fiddle with the identity values (like using DBCC RESEED and such), there's nothing in the IDENTITY column code that stops SQL Server from issuing a value that's already been assigned before. .....– marc_s
Nov 12 at 19:39
What is disallowed is specifying
UNIQUE PRIMARY KEY. There's no technical reason preventing a primary key constraint and a unique constraint from both being defined on a column, but SQL Server will nevertheless disallow it because there's no point. Duplicate indexes are otherwise fine, even if they're exactly the same, and you can even put a unique index on a primary key column if you're so inclined. This is more in line with your static const example.– Jeroen Mostert
Nov 12 at 20:03
What is disallowed is specifying
UNIQUE PRIMARY KEY. There's no technical reason preventing a primary key constraint and a unique constraint from both being defined on a column, but SQL Server will nevertheless disallow it because there's no point. Duplicate indexes are otherwise fine, even if they're exactly the same, and you can even put a unique index on a primary key column if you're so inclined. This is more in line with your static const example.– Jeroen Mostert
Nov 12 at 20:03
add a comment |
1 Answer
1
active
oldest
votes
No, IDENTITY doesn't mean the values will be unique. You can reset the seed of the identity column which will give you duplicates. Or you can enable IDENTITY_INSERT which will allow you to put your own values in.
And IDENTITY is referring to how the values are generated, whereas UNIQUE is adding a constraint. 2 different concepts.
answered Nov 12 at 19:39
Dale Burrell
2,78512147
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53268958%2fwhy-is-identity-unique-even-allowed-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
No, IDENTITY doesn't mean the values will be unique. You can reset the seed of the identity column which will give you duplicates. Or you can enable IDENTITY_INSERT which will allow you to put your own values in.
And IDENTITY is referring to how the values are generated, whereas UNIQUE is adding a constraint. 2 different concepts.
answered Nov 12 at 19:39
Dale Burrell
2,78512147
add a comment |
No, IDENTITY doesn't mean the values will be unique. You can reset the seed of the identity column which will give you duplicates. Or you can enable IDENTITY_INSERT which will allow you to put your own values in.
And IDENTITY is referring to how the values are generated, whereas UNIQUE is adding a constraint. 2 different concepts.
answered Nov 12 at 19:39
Dale Burrell
2,78512147
add a comment |
No, IDENTITY doesn't mean the values will be unique. You can reset the seed of the identity column which will give you duplicates. Or you can enable IDENTITY_INSERT which will allow you to put your own values in.
And IDENTITY is referring to how the values are generated, whereas UNIQUE is adding a constraint. 2 different concepts.
answered Nov 12 at 19:39
Dale Burrell
2,78512147
No, IDENTITY doesn't mean the values will be unique. You can reset the seed of the identity column which will give you duplicates. Or you can enable IDENTITY_INSERT which will allow you to put your own values in.
And IDENTITY is referring to how the values are generated, whereas UNIQUE is adding a constraint. 2 different concepts.
answered Nov 12 at 19:39
Dale Burrell
2,78512147
answered Nov 12 at 19:39
Dale Burrell
2,78512147
answered Nov 12 at 19:39
Dale Burrell
2,78512147
answered Nov 12 at 19:39
Dale Burrell
2,78512147
2,78512147
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53268958%2fwhy-is-identity-unique-even-allowed-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
it's just another constraint. Identity can be disabled for inserts, allowing you to give it explicit values....hence, you can have duplicates. It's just not the same
– Lamak
Nov 12 at 19:38
NO!
IDENTITYalone does NOT say anything about uniqueness! If you fiddle with the identity values (like usingDBCC RESEEDand such), there's nothing in theIDENTITYcolumn code that stops SQL Server from issuing a value that's already been assigned before. .....– marc_s
Nov 12 at 19:39
What is disallowed is specifying
UNIQUE PRIMARY KEY. There's no technical reason preventing a primary key constraint and a unique constraint from both being defined on a column, but SQL Server will nevertheless disallow it because there's no point. Duplicate indexes are otherwise fine, even if they're exactly the same, and you can even put a unique index on a primary key column if you're so inclined. This is more in line with yourstatic constexample.– Jeroen Mostert
Nov 12 at 20:03