Updatable view on base table with identity column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updatable view on base table with identity column

Author

Topic

Dev Null
Starting Member

39 Posts

Posted - 2005-08-16 : 21:53:34

I'm trying to make an updatable view on a table with an identity column, but the view keeps complaining that the identity column can't be null (where the table itself allows this fine.)

The situation: I have a series of devices, distinguished by a unique name, each of which belongs to a cluster, each of which is itself distinguished by a unique name. However, both cluster names and device names are long and referenced many times by other tables; to save storage space we tie each name to an identity key.

A simplified example:

create table cluster (
  id int identity unique,
  name varchar(1000) primary key
)

create table device_storage_test (
  id int identity unique,
  name varchar(1000) primary key,
  cluster_id int foreign key references cluster (id )
)

create view device_test 
as select device_storage_test.id,
  device_storage_test.name,
  cluster.name as cluster
from device_storage_test inner join cluster
    on cluster_id = cluster.id

create trigger device_test_insert
on device_test instead of insert
as
 begin
  -- insert the cluster if it does not already exist
  insert into cluster ( name )
    select inserted.cluster
      from inserted left outer join cluster
        on inserted.cluster = cluster.name
      where cluster.name is null
  -- insert the device
  insert into device_storage_test (name, cluster_id)
    select inserted.name, cluster.id
      from inserted left outer join cluster
        on inserted.cluster = cluster.name
  end

Using

insert into device_storage_test (name, cluster_id) values ('device1', 1)

works fine (so long as cluster_id 1 exists) but

insert into device_test (name, cluster) values ('device2', 'cluster2')

results in the message: "The column 'id' in table 'device_test' cannot be null."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-08-16 : 22:08:35

It is because the optimser can't interpret the default definition of the VIEW column. You have to hack the view a bit...


create view device_test 
as select coalesce(device_storage_test.id,NULL) AS id,
  device_storage_test.name,
  cluster.name as cluster
from device_storage_test inner join cluster
    on cluster_id = cluster.id
GO

HTH

DavidM

A front-end is something that tries to violate a back-end.

Dev Null
Starting Member

39 Posts

Posted - 2005-08-16 : 23:41:00

Brilliant! Thanks heaps.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-17 : 01:23:50

Isnull(device_storage_test.id,NULL) will also work in this case

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources