将另一个主键添加到唯一的表中

发布于 2024-10-20 19:42:40 字数 398 浏览 2 评论 0原文

我在向表中添加另一个主键时遇到问题。 我有 3 列:

  1. 帐户 ID(身份)
  2. 电子邮件 ID
  3. 数据字段

当我制作表格时,我使用此字段来使帐户 ID 和电子邮件 ID 唯一。

PRIMARY KEY (AccountID, EmailID)

我以为这会让我的 emailid 独一无二,但是当我尝试插入具有相同 emailid 的另一行后,它就通过了。 所以我想我错过了一些东西。

现在我的问题是:

  1. 如果我必须使用 alter,如何更改表/PK 约束来修改 EmailID 字段并使其唯一?
  2. 如果我决定删除该表并创建一个新表,如何使这两个主键唯一?

I'm having problems with adding another primary key to my table.
I have 3 columns:

  1. Account ID (Identity)
  2. EmailID
  3. Data field

When I made the table I had this to make the Account ID and the Email ID unique.

PRIMARY KEY (AccountID, EmailID)

I thought that would make my emailid unique, but then after I tried inserting another row with the same emailid it went through.
So I thought I missed something out.

Now for my question:

  1. IF, I had to use alter, how do I alter the table/PK Constraint to modify the EmailID field and make it Unique?
  2. IF I decided to drop the table and made a new one, how do I make those two primary keys unique?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

源来凯始玺欢你 2024-10-27 19:42:40

您可以更改表并添加新的 唯一约束EmailID 列上的

-- This will create a constraint which enforces that the field EmailID
-- have unique values
ALTER TABLE Your_Table_Name
ADD CONSTRAINT unique_constraint_name UNIQUE (EmailID)

但值得注意的是,更改表以添加此新的唯一约束并不意味着您必须删除为 (AccountID, EmailID) 对添加的其他 PRIMARY KEY 约束。当然,除非您的业务逻辑要求这样做。

当您对(AcountID、EmailID)进行主键分组时,它指定 AcountID 和 EmailID 都参与唯一标识该表中的每个单独记录。因此,这意味着表中可以有以下记录:

 AccountID  |  EmailID                  |  Other Fields
----------------------------------------------------------
 100        |  [email protected]         |     ....
 101        |  [email protected]      |     ....
 100        |  [email protected]  |     ....

在前面的示例中,可能有两条具有相同 AccountID 的记录,这是有效的,因为 PRIMARY KEY 指定只有 (AccountID, EmailID) 对具有独一无二——确实如此。没有规定AccountID独立唯一。

总之,您可能想在 AccountID 上添加另一个 UNIQUE 约束。或者简单地将 AccountID 设为 PRIMARY KEY,然后在 EmailID 上添加 UNIQUE 约束。

You may ALTER the table and add a new UNIQUE CONSTRAINT on the EmailID column.

-- This will create a constraint which enforces that the field EmailID
-- have unique values
ALTER TABLE Your_Table_Name
ADD CONSTRAINT unique_constraint_name UNIQUE (EmailID)

It's worth noting though, that altering the table to add this new unique constraint doesn't mean that you have to drop the other PRIMARY KEY constraint that you have added for the (AccountID, EmailID) pair. That is, of course, unless your business logic dictates it.

When you make the grouping of (AcountID, EmailID) the PRIMARY KEY it specifies that both the AcountID and EmailID participate in uniquely identifying each individual record in that table. So, that means that you could have the following records in the table:

 AccountID  |  EmailID                  |  Other Fields
----------------------------------------------------------
 100        |  [email protected]         |     ....
 101        |  [email protected]      |     ....
 100        |  [email protected]  |     ....

In the previous example it is possible to have two records with the same AccountID, and that is valid because the PRIMARY KEY specifies that only the (AccountID, EmailID) pair has to be unique - which it is. It makes no stipulation about AccountID being unique independently.

In conclusion, you probably want to add yet another UNIQUE constraint on AccountID. Or simply make the AccountID alone the PRIMARY KEY and then add a UNIQUE constraint on EmailID.

鸩远一方 2024-10-27 19:42:40

如果 AccountIDEmailID 都是候选键,则只有一个可以作为 PK,另一个需要唯一约束。

从 SQL Server 的 POV 来看,选择哪一个作为 PK 并不重要。外键可以引用 PK 或唯一约束,但考虑到 PK 默认情况下是聚集索引,选择 AccountID 可能是有意义的,因为这可能更窄且更多稳定的。

If both AccountID and EmailID are candidate keys then only one can be the PK the other one will need a unique constraint.

From the POV of SQL Server it doesn't matter which one you choose as the PK. Foreign Key's can reference either the PK or a unique constraint but given that the PK is the clustered index by default it probably makes sense to choose AccountID as this is presumably narrower and more stable.

拧巴小姐 2024-10-27 19:42:40

听起来主键不正确。更有可能的是,emailID 旨在成为您的自然密钥,但由于某种原因(可能是您组织中的开发标准?),您希望使用代理 ID、AccountID,但您仍然希望电子邮件 ID 和代理 ID 都是唯一的并具有一对一的关系。如果这是真的,那么您的主键应该是 AccountID,并且您应该对 EmailID 设置唯一约束。

It sounds like an incorrect Primary key. It's more likely that emailID is intended to be your natural key but for some reason (maybe a development standard in your organization?) you want to use a surrogate ID, AccountID but you still intend for both email ID and surrogate ID to both be unique and have a one to one relationship. If this is true then your primary key should be AccountID and you should place a unique constraint on EmailID.

诠释孤独 2024-10-27 19:42:40

如果您要重新创建该表,它可能如下所示。我假设 EmailID 引用的是电子邮件表而不是电子邮件地址。

CREATE TABLE dbo.AccountEmails
(
    AccountID int not null identity(1,1),
    EmailID int not null,
    Data varchar(max) null,
    constraint PK_AccountEmails PRIMARY KEY //this is a unique single column primary key
    (
        AccountID
    ),
    constraint FK_AccountEmails_EmailID FOREIGN KEY dbo.Email(EmailID) ON //this makes sure EmailID exists in the Email table
    (
        EmailID
    ),
    constraint UQ_AccountEmails_EmailID UNIQUE //unique single column unique constraint
    (
        EmailID
    ),
    constraint UQ_AccountEmails_AccountID_EmailID UNIQUE //the combination of AccountID and EmailID is also unique
    (
        AccountID,
        EmailID
    )
)

鉴于 AccountID 和 EmailID 都是单独唯一的,我不确定 UQ_AccountEmails_AccountID_EmailID 是否真的有必要。

If you were to recreate the table, it could look like this. I assumed EmailID was referencing an email table instead of being an email address.

CREATE TABLE dbo.AccountEmails
(
    AccountID int not null identity(1,1),
    EmailID int not null,
    Data varchar(max) null,
    constraint PK_AccountEmails PRIMARY KEY //this is a unique single column primary key
    (
        AccountID
    ),
    constraint FK_AccountEmails_EmailID FOREIGN KEY dbo.Email(EmailID) ON //this makes sure EmailID exists in the Email table
    (
        EmailID
    ),
    constraint UQ_AccountEmails_EmailID UNIQUE //unique single column unique constraint
    (
        EmailID
    ),
    constraint UQ_AccountEmails_AccountID_EmailID UNIQUE //the combination of AccountID and EmailID is also unique
    (
        AccountID,
        EmailID
    )
)

Given the fact that AccountID and EmailID are both seperately unique, I'm not sure UQ_AccountEmails_AccountID_EmailID is really necessary.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文