問題描述

如何在具有日期限制的視圖中插入?

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

還是謝謝！

這篇關于T-SQL:UNION ALL 視圖不可更新，因為未找到分區列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！