One column is nullable on the composite key

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andy (net__space_at_hotmail.com)
Date: 08/13/04 

Date: 13 Aug 2004 06:32:06 -0700

Hi All!

I would like to have a composite PK on 3 columns, one of them is null

CREATE TABLE TableA (
                ColA int NOT NULL ,
                ColB int NOT NULL ,
                ColC char (3) NULL ,
                ......
)
GO

ALTER TABLE TableA ADD
        CONSTRAINT TableA_PK PRIMARY KEY CLUSTERED
        (
                ColA,
                ColB,
                ColC
        )
GO

SQL Server does not allow having a composite PK with one nullable column:

What is wrong to have values?
1,100,NULL
1,200,ABC
1,200,ABD
....

Code in C applies to Values in B and for some values in B the code does not exist.

I can work out and define a special Code:
NEV(not existing value), but in general I do not understand this restriction.

Thanks

Relevant Pages

  • Re: One column is nullable on the composite key
    ... Andy writes: ... > I would like to have a composite PK on 3 columns, ... > CREATE TABLE TableA ( ... Books Online for SQL Server SP3 at ...
    (microsoft.public.sqlserver.programming)
  • Re: Most efficient way to process this set of records??? (Bill Vaughn)
    ... I thought Bill's books were strictly for SQL Server. ... What I mean by "Composite" record is a group of columns from several ... Use datarelation between a master to the new detail (as opposed to ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Most efficient way to process this set of records??? (Bill Vaughn)
    ... I am more a dotNet guy than a SQL server guy, this seems for me typical a ... What I mean by "Composite" record is a group of columns from several ... Use datarelation between a master to the new detail (as opposed to ... procedure to setup command parameters and retrieve a master record, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem with a join due to multiple Nulls
    ... Using TableA and TableB below i will demonstrate the problem. ... Please post DDL rather than sketches of tables otherwise ... State what version of SQL Server you are using and specify the content ...
    (comp.databases.ms-sqlserver)
  • Re: Require Solution for this SQL problem
    ... runs every morning and based on some logic dumps rows from TableA to ... Now when I run stored proc I want rows copied as below ... FROM TableB (UPDLOCK) ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)