Is it possible to make a non-nullable column nullable when used in a view? (sql server)

Question

Hi,

To start off I have two tables, PersonNames and PersonNameVariations. When a name is searched, it finds the closest name to one of the ones available in PersonNames and records it in the PersonNameVariations table if it's not already in there.

I am using a stored proc to search the PersonNames for a passed in PersonNameVariationand return the information on both the PersonName found and the PersonNameVariation that was compared to it.

Since I am using the Entity Framework, I needed return a complex type in the Import Function but for some reason it says my current framework doesn't support it. My last option was to use an Entity to return in my stored proc instead.

The result that I needed back is the information on both the PersonName that was found and the PersonNameVariation that was recorded. Since I cannot return both entities, I created a view PersonSearchVariationInfo and added it into my Entity Framework in order to use it as the entity to return.

The problem is that the search will not always return a Person Name match. It needs to be able to return only the PersonNameVariation data in some cases, meaning that all the fields in the PersonSearchVariationInfo pertaining to PersonName need to be nullable.

How can I take my view and make some of the fields nullable? When I do it directly in the Entity Framework I get a mapping error:

Error 4 Error 3031: Problem in mapping fragments starting at line 1202:Non-nullable column myproject_vw_PersonSearchVariationInfo.DateAdded in table myproject_vw_PersonSearchVariationInfo is mapped to a nullable entity property. C:\Users\Administrator\Documents\Visual Studio 2010\Projects\MyProject\MyProject.Domain\EntityFramework\MyProjectDBEntities.edmx 1203 15 MyProject.Domain

Anyone have any ideas?

Thanks,
Matt

Answer 1

No you cannot make a column nullable in a view, if it is non-nullable in the underlying table.

How would you deal with that?? You insert a new row in the view and leave that column NULL, but in the underlying table, you would have to provide a value.... that just won't work.

Answer 2

Perhaps.

When do you want it nullable? Here I use NULLIF to force nullability and assume I have empty string. YMMV of course.

The metadata is correct for the nullability of the column in the view too

CREATE TABLE dbo.Foo (ColNonNull varchar(100) NOT NULL)
GO
INSERT dbo.Foo VALUES (NULL) --fails
GO
INSERT dbo.Foo VALUES ('bar') --works
INSERT dbo.Foo VALUES ('') --works
GO
CREATE VIEW dbo.vwFoo
AS
SELECT NULLIF(ColNonNull, '') AS ColNull FROM dbo.Foo
GO
SELECT * FROM dbo.vwFoo
GO
SELECT
    COLUMNPROPERTY(OBJECT_ID('dbo.Foo'), 'ColNonNull', 'AllowsNull') AS TableColNullable,
    COLUMNPROPERTY(OBJECT_ID('dbo.vwFoo'), 'ColNull', 'AllowsNull') AS ViewColNullable
GO