问题描述
如何在具有日期限制的视图中插入?
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
还是谢谢!