Using T-SQL to build a table-building constraint

Why use SQL statements to build a database table?

Now suppose such a scenario, the company's project after testing no problem to be in the customer's actual environment to demonstrate, then the data need to be ported, now the problem: the customer's database version and the company's development phase using the database incompatible how to migrate?

The effective way is to write a more general SQL statement, after writing it into the *.sql file, finally copied to the client's computer, and execute the SQL statements in the *.sql file, so as to achieve the migration of the background database. So it's important to know how to use SQL statements to create a database, create tables, add constraints, create login accounts, and more!

Create and delete a database by using SQL statements Create a database

Grammar:

Create DATABASE name

On "PRIMARY"

（

< data file parameters > ", ..... N "" < filegroup parameters > "

）

"Log On"

（

{< log file Parameters > ", ..... N "}

）

The specific parameters of the data file are as follows:

("Name= Logical file name,"

Filename= Physical File name

", size= size"

", maxsize={Maximum capacity |unlimited}"

", filegrowth= Growth") ", ..... N

The specific parameters of the filegroup are as follows:

Filegroup file group name < file parameters > ", ..... N

Where "" "represents the optional part," {} "indicates the required part. The meanings of each parameter are as follows:

Database name: The name of the database with a maximum of 128 characters.

Primary: The give option is a keyword that specifies the files in the primary filegroup.

Log on: Indicates a clear definition of the transaction log file.

Name: Specifies the logical name of the database, which is the name that is used in SQL Server, and is the identifier of the database in SQL Server.

FileName: Specifies the operating system file name and path for the file that contains the database, which corresponds to the logical name one by one of the name.

Size: Specifies the initial capacity of the database.

MaxSize: Set the maximum value that the operating system file file can grow to.

FILEGROWTH: Specifies the size of the file each time it grows, indicating that the file does not grow when the specified data is 0 o'clock.

Tip: A database can have multiple data files (one for the primary data file and the other for secondary data files) and multiple log files, but no data files and log files.

Deleting a database

When we create a database, it needs to be deleted before it can be successfully created if the database already exists.

Grammar:

Drop database name

So how do you detect if the database exists?

When SQL Server stores the inventory of the database in the sysdatabases table of the Master System database, you can simply use the SELECT statement to see if the database's records exist in the sysdatabases table.

Demo

 UseMasterGo --The batch statement is separate from the following T-SQL statement
 execsp_configure'Show advanced Options',1--set the "Show advanced Options" parameter to get permission to modify advanced options for the "Sp_cmdshell" system
Go
 Reconfigure --Commit Action
Go
 execsp_configure'xp_cmdshell',1--allow SQL Server to invoke operating system commands outside of the database
 Go
 Reconfigure 
Go 
execxp_cmdshell'mkdir E:\text'--Create folder "text" in e-drive
Go
 --detects if a database already exists in the database Text_data
 if exists(Select *  fromsysdatabaseswhereName='Text_data') 
Drop DatabaseText_data--Delete Database Text--data
--Create a database Text--data
Create DatabaseText_data on Primary(Name='text_data1', filename='E:\text\text_data1.mdf', size=10MB, MaxSize=50MB, FileGrowth=3MB), (name='Text_data2', filename='E:\TEXT\TEXT_DATA2.NDF'
 )
 Log  on(Name='Text_log', filename='E:\text\text_log.ldf', size=3MB, maxsizes=10MB, FileGrowth=1MB)

Create and delete table creation tables using SQL statements

Create Tabe Table name

（

The characteristics of column 1 data type columns,

The characteristics of column 2 data type columns,

......

）

Where "column characteristics" includes whether it is empty (null), whether it is an identity column (auto-numbering (identity)), whether there is a default value, and whether it is the primary (PRIMARY KEY), etc.

Delete a table

Same as creating a database if the table already exists we need to delete the table before creating

Grammar:

DROP table Name

The list of tables is stored in the system table sysobjects of the library.

Demo

 UseText_dataGo
if exists(Select *  fromsysobjectswhereName='TextInfo')--detecting the presence of a textinfo table
Drop TableTextInfo--Delete Table TextInfo
Create TableTextInfo--Create a table TextInfo
(Tidint Identity(1,1)Primary Key  not NULL,--primary key, identity column, not emptyTinfonvarchar( $) not NULL--cannot be empty)

Add constraint

Grammar:

ALTER TABLE table name

Add constraint constraint name constraint type specific constraint description

Demo

Alter  Table TextInfo-- Adding a constraint to the tables add
constraintforeignkey  references texttype (typeid)-- add an external constraint

Delete Constraint

Grammar:

ALTER TABLE table name

DROP CONSTRAINT constraint name

Demo

Alter  Table TextInfo-- tables to delete constraints
dropconstraint-- constraints to delete 

Using T-SQL to build a table-building constraint