SQL Server/T-SQL Tutorial/Constraints/Primary Key

Define constraint name for primary key

2> CREATE TABLE customer
3> (
4> cust_id      int          IDENTITY  NOT NULL
5>                           CONSTRAINT cust_pk PRIMARY KEY,
6> cust_name    varchar(30)  NOT NULL
7> )
8> GO
1>
2> EXEC sp_helpconstraint customer
3> GO
Object Name

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
customer


constraint_type                                                                                                                                    constraint_name
                                                                            delete_action update_action status_enabled status_for_replication constraint_keys





-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
PRIMARY KEY (clustered)                                                                                                                            cust_pk
                                                                            (n/a)         (n/a)         (n/a)          (n/a)                  cust_id






No foreign keys reference table "customer", or you do not have permissions on referencing tables.
1>
2> drop table customer;
3> GO

NONCLUSTERED PRIMARY KEY

4> CREATE TABLE MyTable (
5>   MyID Int IDENTITY(1,1) NOT NULL
6>  , Description nVarChar(50) NOT NULL
7>  , Region nVarChar(10) NOT NULL DEFAULT "PNW"
8>  , CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED (MyID))
9> GO
1>
2>
3> drop table MyTable;
4> GO

Primary Key Constraint

A table"s primary key is the primary value that is used to uniquely identify every row in the table.
The primary key designation is specified as a constraint on the table.
Constraints can be created during the initial CREATE TABLE statement or can be added later with an ALTER TABLE statement.
9>
10> CREATE TABLE MyTable (MyID Int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ID PRIMARY KEY
11>           , Description nVarChar(50) NOT NULL
12>           , Region nVarChar(10) NOT NULL DEFAULT "PNW" )
13> GO
1>
2> drop table MyTable;
3> GO
1>

PRIMARY KEY (cust_id)

2> CREATE TABLE customer
3> (
4> cust_id      int          IDENTITY  NOT NULL,
5> cust_name    varchar(30)  NOT NULL,
6>                           PRIMARY KEY (cust_id)
7> )
8> GO
1>
2> EXEC sp_helpconstraint customer
3> GO
Object Name

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
customer


constraint_type                                                                                                                                    constraint_name
                                                                            delete_action update_action status_enabled status_for_replication constraint_keys





-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
PRIMARY KEY (clustered)                                                                                                                            PK__customer__3C00B29C
                                                                            (n/a)         (n/a)         (n/a)          (n/a)                  cust_id






No foreign keys reference table "customer", or you do not have permissions on referencing tables.
1>
2> drop table customer;
3> GO

The PRIMARY KEY Clause

The PRIMARY KEY clause has the following form:
      [CONSTRAINT c_name]
      PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name1 [{,col_name2} ...])

11>
12> CREATE TABLE employee (emp_no INTEGER NOT NULL,
13>               emp_fname CHAR(20) NOT NULL,
14>               emp_lname CHAR(20) NOT NULL,
15>               dept_no CHAR(4) NULL,
16>       CONSTRAINT prim_empl PRIMARY KEY (emp_no))
17>
18> drop table employee;
19> GO
1>

Using a Multicolumn Primary Key

5> CREATE TABLE ClassGrades(
6>     ClassID int,
7>     StudentID int,
8>     GradeLetter varchar(2),
9>     Constraint PK_ClassGrades
10>         PRIMARY KEY(ClassID, StudentID)
11> )
12> GO
1>
2> INSERT ClassGrades VALUES(1,1,"A")
3> INSERT ClassGrades VALUES(1,2,"B-")
4> INSERT ClassGrades (ClassID, GradeLetter)VALUES(1,"C-")
5> GO
(1 rows affected)
(1 rows affected)
Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Line 4
Cannot insert the value NULL into column "StudentID", table "master.dbo.ClassGrades"; column does not allow nulls. INSERT fails.
The statement has been terminated.
1> drop table ClassGrades;
2> GO

You cannot add another primary key to table

5>
6> CREATE TABLE MyTable (
7>   MyID Int IDENTITY(1,1) NOT NULL , ID Int NOT NULL
8>  , Description nVarChar(50)
9>  , Region nVarChar(10) DEFAULT "PNW"
10>  , CONSTRAINT MyTable_PK PRIMARY KEY NONCLUSTERED (MyID,Region))
11> GO
1>
2> ALTER TABLE MyTable
3> ADD CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED (MyID)
4> GO
Msg 1779, Level 16, State 1, Server J\SQLEXPRESS, Line 2
Table "MyTable" already has a primary key defined on it.
Msg 1750, Level 16, State 1, Server J\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
1>
2> drop table MyTable;
3> GO
1>