Can't create primary key

[Thargy]

I have noticed a quirk in the primary key creation of tables.

DROP TABLE PK_TEST

CREATE TABLE PK_TEST
(DUMMY_COLUMN VARCHAR(100))

ALTER TABLE PK_TEST
ADD CONSTRAINT NN_PK_TEST_DUMMY_COLUMN
CHECK (DUMMY_COLUMN IS NOT NULL)

ALTER TABLE PK_TEST
ADD CONSTRAINT PK_PK_TEST
PRIMARY KEY(DUMMY_COLUMN)

results in the following error message

[red]Msg 8111, Level 16, State 1, Line 8
Cannot define PRIMARY KEY constraint on nullable column in table 'PK_TEST'.
Msg 1750, Level 16, State 0, Line 8
Could not create constraint. See previous errors.[/red]

This seems bizarre, as by definition, a primary key is not null and unique. If however I put the not null constraint in the table whilst creating it, as shown below, the command completes successfully.

DROP TABLE PK_TEST

CREATE TABLE PK_TEST
(DUMMY_COLUMN VARCHAR(100) NOT NULL)

--ALTER TABLE PK_TEST
--ADD CONSTRAINT NN_PK_TEST_DUMMY_COLUMN
--CHECK (DUMMY_COLUMN IS NOT NULL)

ALTER TABLE PK_TEST
ADD CONSTRAINT PK_PK_TEST
PRIMARY KEY(DUMMY_COLUMN)

I surmise that SQL server is making different entries in the data dictionary, and thereby distinguishing between not null applied when a table is made, and not null added afterwards as a user constraint.

Can anyone explain this peculiar behaviour?

Regards

T

 

[gmmastros]

Clearly, they are handled differently. I'm not exactly sure how, but I can see some differences in the data dictionary. In the first example, you are creating a constraint. In the second, you are creating a key.

When you create a constraint, the column definition still allows NULLS, but the constraint doesn't. When you specify NOT NULL on the create table, the column doesn't allow nulls.

DROP TABLE PK_TEST
DROP TABLE PK_TEST2

-- Create PK_TEST
CREATE TABLE PK_TEST
(DUMMY_COLUMN VARCHAR(100))

-- ADD the constraint
ALTER TABLE PK_TEST
ADD CONSTRAINT NN_PK_TEST_DUMMY_COLUMN
CHECK (DUMMY_COLUMN IS NOT NULL)

-- Create PK_Test2
CREATE TABLE PK_TEST2
(DUMMY_COLUMN VARCHAR(100) NOT NULL)

-- Add the Primary Key
ALTER TABLE PK_TEST2
ADD CONSTRAINT PK_PK_TEST
PRIMARY KEY(DUMMY_COLUMN)

-- Check the 'Allows Null' property
Select COLUMNPROPERTY(Object_ID('PK_Test'), 'DUMMY_COLUMN', 'AllowsNull') As PK_TEST,
       COLUMNPROPERTY(Object_ID('PK_Test2'), 'DUMMY_COLUMN', 'AllowsNull') As PK_TEST2

I suspect this doesn't really help. But it is something to consider.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Thargy]

Thanks for having a look George.

I'm so used to doing this in Oracle that it threw me for a while. I thought it was finger trouble at first, but now I realise that it's just something else for me to learn about SQL Server.



Regards

T

 

[tlbroadbent]

Creating the table with the correct attribute is probaly the best solution. If you are working with an existing table, you should alter the column and make it not nullable, rather than create a contraint. Then you will be able to create the primary key.

ALTER TABLE PK_TEST ALTER COLUMN DUMMY_COLUMN VARCHAR(100) NOT NULL

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

 

[Thargy]

terry,

thanks for that. Part of my reason for asking is that I am writing a tool to automatically script our legacy sql server databases to a bunch of files, each of which relates to a particular item in the db.

I want to get all my non null, unique and check constraints in one place, all my create tables in another, and all my primary keys in a third. I am using microsoft's publishing wizard to produce a large text file, and then processing that file.

Ideally, I want the pk creation done in one place, with a naming convention applied, i.e. every primary key is called PK_<TABLE_NAME>. When inspecting sysobjects, it's much easier to read named items than system named items. I therefore ask the following:-

Using the ALTER TABLE method, is it possible to name the constraint, as I did above when I created a separate user constraint? Alternatively, can someone point me to the syntax diagrams for sql server ALTER TABLE statements?

Regards

T

 

[jbenson001]

Alternatively, can someone point me to the syntax diagrams for sql server ALTER TABLE statements?

If you have managment studio installed, then you have the help files otherwise you can use MSDN:

http://msdn.microsoft.com/en-us/library/ms130214(SQL.90).aspx