SQL Server/T-SQL/Constraints/Primary key

Материал из SQL эксперт
Перейти к: навигация, поиск

Combine two columns as the primary key

   <source lang="sql">

1> 2> CREATE TABLE ClassGrades( 3> ClassID int, 4> StudentID int, 5> GradeLetter varchar(2), 6> Constraint PK_ClassGrades PRIMARY KEY(ClassID, StudentID) 7> ) 8> GO 1> 2> INSERT ClassGrades VALUES(1,1,"A") 3> GO (1 rows affected) 1> INSERT ClassGrades VALUES(1,2,"B-") 2> GO (1 rows affected) 1> INSERT ClassGrades (ClassID, GradeLetter) VALUES(1,"C-") 2> GO Msg 515, Level 16, State 2, Server sqle\SQLEXPRESS, Line 1 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> select * from ClassGrades 2> GO ClassID StudentID GradeLetter


----------- -----------
         1           1 A
         1           2 B-

(2 rows affected) 1> 2> drop ClassGrades 3> GO Msg 102, Level 15, State 1, Server sqle\SQLEXPRESS, Line 2 Incorrect syntax near "ClassGrades". 1> 2>

      </source>
   
  


Define Primary key in table creation command

   <source lang="sql">

20> 21> CREATE TABLE employee (emp_no INTEGER NOT NULL, 22> emp_fname CHAR(20) NOT NULL, 23> emp_lname CHAR(20) NOT NULL, 24> dept_no CHAR(4) NULL, 25> CONSTRAINT prim_empl PRIMARY KEY (emp_no)) 26> GO 1> 2> drop table employee 3> GO 1> 2> -- 3>

      </source>
   
  


Specification of the primary key of the employee table as a column-level constraint

   <source lang="sql">

1> -- Specification of the primary key of the employee table as a column-level constraint. 2> CREATE TABLE employee 3> (emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY, 4> emp_fname CHAR(20) NOT NULL, 5> emp_lname CHAR(20) NOT NULL, 6> dept_no CHAR(4) NULL) 7> GO 1> drop table employee 2> GO 1>

      </source>