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>