Kjell Rilbe wrote:

> Just thought I'd tell you about an annoying experience with SQL Server I
> just had.

[snip]

Just had another unnerving experience.

First:
create table "Persons" (
"CompanyId" int not null,
"PersonId" int,
"Name" nvarchar(50),
constraint "PersonsPK" primary key ("CompanyId", "PersonId")
)

Executes without error.
Then:
insert into "Persons" ("CompanyId", "PersonId", "Name")
values (123, null, 'New Contact, id unassigned')

Bang! Message:

Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'PersonId', table
'Database.dbo.Persons'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Huh? But the create table clearly says that PersonId is nullable...?

Apparently SQL Server *silently* converts a nullable column to not null
if it's used in a primary key. I would have expected the create table to
fail instead of tricking me into thinking my incorrect SQL is correct.

*sigh*

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64