SQL Server/T-SQL Tutorial/Constraints/Primary Key
Содержание
Define constraint name for primary key
<source lang="sql">
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</source>
NONCLUSTERED PRIMARY KEY
<source lang="sql">
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</source>
Primary Key Constraint
<source lang="sql">
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></source>
PRIMARY KEY (cust_id)
<source lang="sql">
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</source>
The PRIMARY KEY Clause
<source lang="sql">
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></source>
Using a Multicolumn Primary Key
<source lang="sql">
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</source>
You cannot add another primary key to table
<source lang="sql">
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></source>
