T-SQL : UNION ALL view not updatable because a partitioning column was not found

Question

How can I insert in a view with date constraints?

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

Table 1:

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`

Table 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`

View creation:

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

Insert gives error:

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

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

Solution 1

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

Thanks anyway!

Solution 2

this example can give a solution for your problem

create table partA
    (
    partId int,
    type varchar(10) constraint CKpartA_type check (type = 'partA'),
    value int,
    constraint PKpartA primary key(partId, type),
    )
    create table partB
    (
    partId int,
    type varchar(10) constraint CKpartB_type check (type = 'partB'),
    value int,
    constraint PKpartB primary key(partId, type)
    )
    go
    create view part
    as
    select partId, type, value
    from partA
    union all
    select partId, type, value
    from partB
    go


    insert into part
    select 1,'partB',1
    union all
    select 2,'partA',2
    go
    update part
    set value = 20
    go

    select *
    from part

    go
    delete from part
    go

Comments

[Claudiu Haidu]

and the interval is not correct technically...it should have been greater or equal at one end : (([date]<'2014-01-12 00:00:00.000' AND [date]>='2014-01-11 00:00:00.000'))

[stubs]

Thanks. So basically you need a fixed value in the partitioned tables' primary keys?