none
Explicitly defining Clustered Index and NOT NULL constraint on Primary Key column

    Question

  • In the following table definition, is it necessary to explicitly define clustered index and NOT NULL constraint on Primary Key column, since defining Primary Key alone will do the job.

    create table t1 (
    col1 char(4) Not Null Primary Key Clustered,
    col2 varchar(20)
    )

    Saturday, December 04, 2010 8:44 AM

Answers

  • Yes... they are not required, When you declare a primary key, in default it set it as Not null and create an clustered index. so just setting primary key will be enough.
    Saturday, December 04, 2010 8:50 AM
  • Nevertheless, it is better to be explicit, not the least with regards to NOT NULL. Later you may decide to change the key - add one more column, or introduce a surrogate key - and if you script is missing NOT NULL, you may be in for nasty surprises.

    When it comes to CLUSTERED, I will have to admit that I typically only add NONCLUSTERED when I want something else to be clustered. However, if you add keys separately in a script with ALTER TABLE statements, I would suggest that you should include CLUSTERED if you want the key to be clustered, so that you get an error if there mistakenly already is a clustered index on the table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, December 04, 2010 10:47 AM

All replies

  • Yes... they are not required, When you declare a primary key, in default it set it as Not null and create an clustered index. so just setting primary key will be enough.
    Saturday, December 04, 2010 8:50 AM
  • Nevertheless, it is better to be explicit, not the least with regards to NOT NULL. Later you may decide to change the key - add one more column, or introduce a surrogate key - and if you script is missing NOT NULL, you may be in for nasty surprises.

    When it comes to CLUSTERED, I will have to admit that I typically only add NONCLUSTERED when I want something else to be clustered. However, if you add keys separately in a script with ALTER TABLE statements, I would suggest that you should include CLUSTERED if you want the key to be clustered, so that you get an error if there mistakenly already is a clustered index on the table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Saturday, December 04, 2010 10:47 AM