SQL CREATE TABLE Script

"1" SQL Server setting primary key self-growing column SQL Server setting primary key self-growing column 1. Create a new data table with field ID, set ID as primary key www.2cto.com CREATE TABLE TB (ID int,constraint pkid PR Imary key (ID)) CREATE TABLE TB (ID int primary key) 2. Create a new data table with field ID, set ID as primary key and auto-number CREATE TABLE TB (ID int identity (), cons Traint Pkid primary key (ID)) CREATE TABLE TB (ID int identity (first) primary key) 3. A data table has been built with field IDs, with ID set to primary key ALTER TABLE TB a Lter column ID int not NULL ALTER TABLE TB ADD constraint Pkid primary key (ID) 4. Delete primary key www.2cto.com Declare @Pk VarChar ( 100); Select @Pk =name from sysobjects where parent_obj=object_id (' TB ') and xtype= ' Pk '; if @Pk are not nullexec (' Alter table TB Dro P ' + @Pk) "2" create foreign keys in new table

2. In Object Explorer, connect to an instance of the database engine.

4. On the Standard menu bar, click New Query.

5. Copy and paste the following example into the Query window, and then click"Execute".This instance creates a table and defines the columntempid , which references  sales.salesreason  table   salesreasonid.  sales.salesreas On table is automatically propagated to the sales.tempsalesreason table. " >on DELETE CASCADE and on UPDATE CASCADE clauses are used to ensure that changes to  sales.salesreason  tables are automatically propagated to  sales.tempsalesreason  table

   Use AdventureWorks2012; Gocreate TABLE Sales.tempsalesreason (tempid int not NULL, Name nvarchar (), CONSTRAINT pk_tempsales PRIMARY KEY nonclust     Ered (tempid), CONSTRAINT Fk_tempsales_salesreason FOREIGN KEY (tempid) REFERENCES Sales.salesreason (Salesreasonid) On DELETE CASCADE on UPDATE CASCADE); GO

"3" To make the necessary batch before making the table create table Usercontactperson
(
ID INT PRIMARY KEY,
UserID INT,
Contactpersonid INT

)

(The code is the format of the dislocation) this writing is the most primitive, backward. is also the most prone to error writing: First it is not specified in the code in the database execution, sometimes people are very careless, open the file is executed, it is possible to build the table to master or other database. Second, when the batch is built, if the table already exists in the database. will affect the execution. So we can improve the way it is written.

Use Myassistant
GO

IF EXISTS (SELECT 1 from SYSOBJECTS WHERE NAME = ' Usercontactperson ' and XTYPE = ' U ')

DROP TABLE Usercontactperson;
CREATE TABLE Usercontactperson
(
ID INT PRIMARY KEY,
UserID INT,
Contactpersonid INT

)
GO

This writing also has a problem, is drop TABLE Usercontactperson; This place is generally not recommended after you delete a table, and then create a new table, unless the database is created. The reason is not to say much, presumably we all understand very well. You should always be prompted to let the person executing the script choose to handle it, similar to the following wording

IF not EXISTS (SELECT 1 from SYSOBJECTS WHERE NAME = ' Users ' and XTYPE = ' U ')

CREATE TABLE [Users]
(
[UserID] INT IDENTITY (--id), primary key
[UserName] NCHAR (15),--User name
[Password] CHAR (15),--Password
[Nickname] NCHAR (15),--alias, net name
[Name] NCHAR (8),--real name
[SEX] BIT,--gender
[Age] SMALLINT,--age
[Birthday] smalldatetime,--Birthday
[Moblie] CHAR (11),--Mobile
[Officephone] CHAR (12), Office landline
[HomePhone] CHAR (12),--Home landline
[Email] NCHAR (30),--e-mail
[QQ] CHAR (Ten),--QQ
[MSN] CHAR (--MSN),
[Skype] CHAR (+),--skype
[Personwebsit] CHAR (20),--Personal homepage
[Schoole] NCHAR (20),--Graduate school
[Place] NCHAR (15),--Hometown
[Hometown] NCHAR (25),--Hometown
[CreateDate] smalldatetime,--User creation time
[Updatedate] smalldatetime,--User profile update time
[Lastlogin] smalldatetime,--User last login time
[IP] CHAR (15),--User login IP
[Lock] BIT,--whether the user is locked out
[ISAdmin] BIT,--whether it is an administrator
[Session] CHAR (20),---Save login Seesion
CONSTRAINT Pk_users_userid PRIMARY KEY (UserID)
)
ELSE
--drop TABLE Users
PRINT ' This table has been existed! Should check and take action '
GO

Here are a few ways to determine if the table exists

1:if not EXISTS (SELECT 1 from SYSOBJECTS WHERE NAME = ' Users ' and XTYPE = ' U ')

2:if EXISTS (SELECT 1 from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ UserGroup] ') and OBJECTPROPERTY (ID, N ' istable ') = 1)

3:if object_id (N ' ContactPerson ') is not NULL