使用SQL Server中的非唯一列对另一个表创建约束

发布于 2025-01-21 05:01:22 字数 2073 浏览 4 评论 0原文

我有两个名为类别和categoryAuthorizations的SQL Server表。我想确保所有categoryAuthorizations.CategoryId值均在categories.categoryid中。两个表中的ID列是主键。两个表中的categoryId列都是non-Inique。因此，我不能使用外键约束来确保categoryId上的参考完整性。

CREATE TABLE [dbo].[Categories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CategoryId] [int] NOT NULL,
    [Category] [varchar](500) NOT NULL,
    [Status] [bit] NOT NULL,
    [Updates] [smallint] NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)

CREATE TABLE [dbo].[CategoryAuthorizations](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CategoryId] [int] NOT NULL,
    [AuthCode] [varchar](10) NOT NULL,
    [Status] [bit] NOT NULL,
    [Updates] [smallint] NOT NULL,
 CONSTRAINT [PK_CategoryAuthorizations] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)

示例：

表：类别

ID	类别	类别	状态	更新
1	1	胎	1	0
2	1	轮胎	0	1
3	1	轮胎	1	2
4	2	制动器	1	0

表：类别authorizations

id	categoryId	authcode	状态
1	1	SAM	1
2	2	C chr	1
3 3	3	Sam	1

categoryAuthorizations中的最后一个记录，其中categoryAuthorizations.CategoryId = 3永远不要允许BE插入是否应用了约束，因为category.categoryid不包含3个值。

总结：如何有效地在非独特列上应用参考完整性另一个桌子？是否存在可以帮助满足此标准的约束或其他数据库解决方案？

原文

I have two SQL Server tables named Categories and CategoryAuthorizations. I want to ensure all CategoryAuthorizations.CategoryId values are in Categories.CategoryId. The Id column in both tables are Primary Keys. The CategoryId column in both tables are non-unique. Therefore, I cannot use foreign key constraint to ensure referential integrity on CategoryId.

CREATE TABLE [dbo].[Categories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CategoryId] [int] NOT NULL,
    [Category] [varchar](500) NOT NULL,
    [Status] [bit] NOT NULL,
    [Updates] [smallint] NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)

CREATE TABLE [dbo].[CategoryAuthorizations](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CategoryId] [int] NOT NULL,
    [AuthCode] [varchar](10) NOT NULL,
    [Status] [bit] NOT NULL,
    [Updates] [smallint] NOT NULL,
 CONSTRAINT [PK_CategoryAuthorizations] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)

Example:

Table: Categories

Id	CategoryId	Category	Status	Updates
1	1	Tire	1	0
2	1	Tire	0	1
3	1	Tires	1	2
4	2	Brakes	1	0

Table: CategoryAuthorizations

Id	CategoryId	AuthCode	Status
1	1	SAM	1
2	2	CHR	1
3	3	SAM	1

The last record in CategoryAuthorizations, where CategoryAuthorizations.CategoryId = 3, should never be allowed to be inserted if a constraint is applied because Category.CategoryId does not contain a value of 3.

To sum up: How can I effectively apply referential integrity on a non-unique column from another table? Is there a constraint or another database solution which can help meet this criteria?

分享到QQ

分享到微博