SQL Server database creation, tables, constraints (1)

Key points:

1.The Go clause is used to separate the batch processing. The range of local variables is limited to one batch. After the go clause, the previously defined variables cannot be used.

2.Create Database studentmanager and generate the primary data file. Do not add the go statement between the log files. If the go statement is added, an error will be reported. The primary and secondary data files and log files cannot be generated.

 

3. In a database, only one primary data file is allowed.(Extension :.MDF),Multiple Data Files(Extension :.NDF),Multiple log files(Extension:
.LDF)

 

 

 

Studentmanager. MDF

 

Use master <br/> go <br/> -- find all databases and delete studentmanager. <br/> If exists (select * From sysdatabases where name = 'studentmanager ') <br/> drop database studentmanager <br/> go <br/> Create Database studentmanager <br/> -- do not add the go statement here. If the go statement is added, the master data file cannot be generated, and log files </P> <p> On <br/> Primary -- master data file <br/> (<br/> name = 'studentmanager ', <br/> filename = 'd: \ sqlserver \ data \ studentmanager. MDF ', <br/> size = 5 MB, <br/> maxsize = 50 MB, <br/> filegrowth = 1 MB <br/>) <br/> -- data files can be added here with the extension. NDF </P> <p> log on -- Log File <br/> (name = 'studentmanager _ log', <br/> filename = 'd: \ sqlserver \ data \ studentmanager_log.ldf ', <br/> size = 5 MB, <br/> maxsize = 50 MB, <br/> filegrowth = 1 MB <br/>) <br/> -- you can add multiple log files with the extension. LDF <br/> go </P> <p> Use studentmanager <br/> go </P> <p> -- create a master table <br/> Create Table student (< br/> -- field name data type constraints (only non-empty constraints are added here) <br/> stuid int identity not null, -- id ID auto-increment 1 <br/> stuname varchar (10) Not null, <br/> stuage int not null, <br/> stutel varchar (11) not null, <br/> stuaddress varchar (20), <br/> groupid int not null <br/>) <br/> go </P> <p> -- create a sub-Table <br/> Create Table exam (</P> <p> examid int identity not null, <br/> stuid int not null, -- foreign key <br/> writeresult int, <br/> computerresult int <br/>) <br/> go </P> <p> -- add constraints to a table <br/> alter table student <br/> Add constraint pk_stuid <br/> Primary Key (stuid ), -- primary key constraint <br/> constraint ch_stuage <br/> check (stuage> = 0 and stuage <= 60 ), -- check constraint <br/> constraint ch_stutel -- check constraint <br/> check (stutel like '[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] '), -- The check constraint uses wildcards </P> <p> constraint de_stuaddress -- default constraint <br/> default 'address unknown 'For stuaddress <br/> go <br/> alter table exam <br/> Add constraint pk_examid <br/> Primary Key (examid ), <br/> constraint ch_writeresult <br/> check (writeresult> = 0 and writeresult <= 100 ), <br/> constraint ch_computerresult <br/> check (computerresult> = 0 and computerresult <= 100 ), <br/> -- set the foreign key <br/> constraint exam_stuid <br/> foreign key (stuid) <br/> references student (stuid) <br/> go </P> <p>