SQL Server/T-SQL Tutorial/Table/Create Table

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

A statement that creates a table with column attributes

   <source lang="sql">

4> 5> CREATE TABLE Billings 6> (BillingID INT NOT NULL IDENTITY PRIMARY KEY, 7> BankerID INT NOT NULL, 8> BillingDate SMALLDATETIME NULL, 9> BillingTotal MONEY NULL DEFAULT 0) 10> GO 1> 2> drop table Billings; 3> GO</source>


CLUSTERED column

   <source lang="sql">

3> CREATE TABLE StudentsArchive ( 4> AID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, 5> type nvarchar(6) NOT NULL, 6> whenchanged smalldatetime NOT NULL DEFAULT Getdate(), 7> 8> StudentID int, 9> nFirstName nvarchar(30), 10> nLastName nvarchar(50), 11> oFirstName nvarchar(30), 12> oLastName nvarchar(50) 13> ) 14> GO 1> 2> drop table StudentsArchive; 3> GO 1></source>


Computational column

   <source lang="sql">

6> CREATE TABLE T ( 7> int1 int, 8> bit1 bit, 9> varchar1 varchar(3), 10> dec1 dec(5,2), 11> cmp1 AS (int1 + bit1) 12> ) 13> GO 1> 2> INSERT T (int1, bit1) VALUES (1, 0) 3> INSERT T (int1, varchar1) VALUES (2, "abc") 4> INSERT T (int1, dec1) VALUES (3, 5.25) 5> INSERT T (bit1, dec1) VALUES (1, 9.75) 6> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> drop table t; 3> GO</source>


PRIMARY KEY column

   <source lang="sql">

4> CREATE TABLE employee( 5> emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY, 6> emp_fname CHAR(20) NOT NULL, 7> emp_lname CHAR(20) NOT NULL, 8> dept_no CHAR(4) NULL) 9> GO 1> 2> drop table employee; 3> GO 1></source>


Table Creation Script for Parent and Child with FOREIGN KEY Constraint and INSTEAD OF Trigger

   <source lang="sql">

4> CREATE TABLE Parent( 5> ID int NOT NULL PRIMARY KEY 6> ) 7> GO 1> CREATE TABLE Child( 2> ID int NOT NULL PRIMARY KEY REFERENCES Parent (ID) ON DELETE CASCADE 3> ) 4> GO 1> CREATE TRIGGER trd_Parent ON Parent INSTEAD OF DELETE 2> AS 3> IF @@ROWCOUNT = 0 4> RETURN 5> PRINT "Inside Parent trigger." 6> GO 1> INSERT Parent VALUES (1) 2> INSERT Parent VALUES (2) 3> INSERT Parent VALUES (3) 4> INSERT Parent VALUES (4) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> INSERT Child VALUES (1) 2> INSERT Child VALUES (2) 3> INSERT Child VALUES (3) 4> INSERT Child VALUES (4) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> --INSTEAD OF Trigger that Duplicates DELETE Action 2> ALTER TRIGGER trd_Parent ON Parent INSTEAD OF DELETE 3> AS 4> IF @@ROWCOUNT = 0 5> RETURN 6> PRINT "Inside Parent trigger." 7> DELETE P 8> FROM 9> Parent P 10> JOIN 11> deleted D ON D.ID = P.ID 12> GO 1> -- AFTER Trigger on Child Table 2> CREATE TRIGGER trd_Child ON Child AFTER DELETE 3> AS 4> IF @@ROWCOUNT = 0 5> RETURN 6> PRINT "Inside Child trigger." 7> GO 1> 2> drop table child 3> drop table parent 4> GO 1></source>


The basic syntax of the CREATE TABLE statement

   <source lang="sql">

CREATE TABLE table_name (column_name_1 data_type [column_attributes] [, column_name_2 data_type [column_attributes]]... [, table_attributes]) Common column attributes Attribute Description NULL|NOT NULL Indicates whether or not the column can accept null values. If omitted, NULL is the default unless PRIMARY KEY is specified. PRIMARY KEY|UNIQUE Identifies the primary key or a unique key for the table. If PRIMARY is specified, the NULL attribute isn"t allowed. IDENTITY Identifies an identity column. Only one identity column can be created per table. DEFAULT default_value Specifies a default value for the column.</source>