T-SQL:UNION ALL 视图不可更新，因为未找到分区列

2021-09-10 00:00:00 union tsql sql-server view

如何在具有日期限制的视图中插入?

How can I insert in a view with date constraints?

这是我点击脚本作为创建表后得到的表:

Here are my tables resulted after clicking on script as create table :

表一:

CREATE TABLE [dbo].[tbl_zaua_1_17](
    [id] [int] NOT NULL,
    [date] [datetime] NULL,

     CONSTRAINT [PK_tbl_zaua_1_17] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_zaua_1_17]  
WITH CHECK ADD  CONSTRAINT [CK_tbl_zaua_1_17] 

CHECK  (([date]<'2014-01-18 00:00:00.000' AND [date]>'2014-01-16 00:00:00.000'))
GO

ALTER TABLE [dbo].[tbl_zaua_1_17] CHECK CONSTRAINT [CK_tbl_zaua_1_17]
GO`

表2:

CREATE TABLE [dbo].[tbl_zaua_1_11](
    [id] [int] NOT NULL,
    [date] [datetime] NULL,
 CONSTRAINT [PK_tbl_zaua_1_11] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_zaua_1_11]  WITH CHECK ADD  CONSTRAINT [CK_tbl_zaua_1_11] CHECK  (([date]<'2014-01-12 00:00:00.000' AND [date]>'2014-01-10 00:00:00.000'))
GO

ALTER TABLE [dbo].[tbl_zaua_1_11] CHECK CONSTRAINT [CK_tbl_zaua_1_11]
GO`

视图创建:

create view zaua1
as
   select * from [dbo].[tbl_zaua_1_11]
   union all
   select * from [dbo].[tbl_zaua_1_17]`

插入给出错误:

UNION ALL 视图不可更新，因为未找到分区列.

UNION ALL view is not updatable because a partitioning column was not found.

insert into [dbo].[zaua1]
values (3,'2014-01-11')

推荐答案

现在我明白了，应该在两列(id、date)上都设置了主键.

Now I see, should have set primary keys on both columns (id, date).

CREATE TABLE [dbo].[tbl_zaua_1_11](
    [id] [int] NOT NULL,
    [date] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl_zaua_1_11] PRIMARY KEY CLUSTERED 
(
    [id] ASC,
    [date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_zaua_1_11]  WITH CHECK ADD  CONSTRAINT [CK_tbl_zaua_1_11] 

CHECK  (([date]<'2014-01-12 00:00:00.000' AND [date]>'2014-01-10 00:00:00.000'))
GO

ALTER TABLE [dbo].[tbl_zaua_1_11] CHECK CONSTRAINT [CK_tbl_zaua_1_11]
GO

还是谢谢！

相关文章