Hello Kjell,

>> 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.

Primary Key columns are "not null" by default, I bet this is the same for
Firebird and many other systems.

Nothing new here ;-)

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com