MS database, table creation, Constraints

Use mastergoIF EXISTS (SELECT * FROM sys. databases WHERE name = 'stodydb') drop database StudyDB -- if so, delete GOEXEC xp_mongoshell 'mkdir D: \ SQL '-- call the doscommand to CREATE a folder -- CREATE DATABASE StudyDBON PRIMARY (-- PRIMARY can be used to specify the file NAME = 'studydb1 _ data' in the main file group ', -- Logical name of the primary data file FILENAME = 'd: \ SQL \ StudyDB1_data.mdf ', -- physical name SIZE of the primary data file = 3 MB, -- initial size of the primary data file MAXSIZE = 5 MB, -- maximum size of the primary data file FILEGROWTH = 20% -- growth rate of the primary data file), -- Note ", "Number separation (NAME = 'studydb2 _ data', -- Logical NAME of the master data file FILENAME = 'd: \ SQL \ StudyDB2_data.ndf ', -- physical name SIZE of the primary data file = 512 KB, -- initial SIZE of the primary data file MAXSIZE = 5 MB, -- maximum SIZE of the primary data file FILEGROWTH = 20%) log on (NAME = 'studydb1 _ log', FILENAME = 'd: \ SQL \ StudyDB_log1.ldf ', SIZE = 512KB, MAXSIZE = 3 MB, FILEGROWTH = 20%), -- Note ", "Number separation (NAME = 'studydb2 _ log', FILENAME = 'd: \ SQL \ StudyDB_log2.ldf ', SIZE = 512KB, MAXSIZE = 3 MB, FILEGROWTH = 20%) go -- create a table use StudyDB -- you must use the database StudyDB. Otherwise, the table you created will be in the master database goIF EXISTS (SELECT * FROM sys. objects WHERE name = 'info') drop table stuInfo -- CREATE master TABLE stuInfo -- create table stuInfo (stuName NVARCHAR (20) not null, stuNo NCHAR (6) not null, stuSex NCHAR (4) not null, stuAge smallint not null, stuSeat smallint identity (1, 1), stuAddress NTEXT) GO -- create constraints for the primary table stuInfo [ADD constraints outside] -- alter table stuInfo add constraint PK_stuNo primary key (stuNo), -- primary key constraint UQ_stuNo UNIQUE (stuNo ), -- the unique CONSTRAINT CK_stuNo CHECK (stuNo LIKE 's253 [0-9] [0-9] '), -- CHECK constraints CONSTRAINT CK_stuSex CHECK (stuSex = 'male' OR stuSex = 'female '), CONSTRAINT CK_stuAge CHECK (stuAge BETWEEN 15 AND 40 ), CONSTRAINT CK_stuSeat CHECK (stuSeat <= 30), CONSTRAINT DF_stuAddress DEFAULT ('address unknown ') FOR stuAddress -- default value: GO -- if you ADD the constraint alter table stuInfo with nocheck to existing data -- do not CHECK existing data add constraint my_cus CHECK (stuSex IN (1, 2 )) alter table stuInfoNOCHECK | check constraint my_cuz -- use the defined my_cuz CONSTRAINT to expire and take effect. -- CREATE a slave TABLE stuMarks -- create table stuMarks (ExamNo CHAR (7) not null, stuNo NCHAR (6) not null, writtenExam smallint not null, LabExam smallint not null) GO -- create constraints for the stuMarks TABLE -- alter table stuMarks add constraint PK_ExamNo primary key (ExamNo ), CONSTRAINT CK_ExamNo CHECK (ExamNo LIKE 's2718 [0-9] [0-9] '), CONSTRAINT FK_stuNo foreign key (stuNo) REFERENCES stuInfo (stuNo ), -- foreign key constraints CONSTRAINT CK_writtenExam CHECK (writtenExam BETWEEN 0 AND 100), CONSTRAINT DF_writtenExam DEFAULT 0 FOR writtenExam, CONSTRAINT CK_LabExam CHECK (LabExam BETWEEN 0 AND 100 ), CONSTRAINT DF_LabExam DEFAULT 0 FOR LabExamGO create table TBL_TEST1 (TEST1_ID int primary key identity (1, 1) -- primary key, auto-increment, TEST_NAME VARCHAR (20) not null) create table TBL_TEST2 (TEST2_ID int primary key identity (1, 1), TEST1_ID int references TBL_TEST1 (TEST1_ID) -- foreign KEY constraint, TEST2_NAME VARCHAR (20) UNIQUE, TEST2_SEX CHAR (2) CHECK (TEST2_SEX = 'male' OR TEST2_SEX = 'female ') -- OR: CHECK TEST2_SEX IN ('male', 'female '), TEST2_ADDRESS text default ('address unknown ') insert into TBL_TEST1 values ('1') select * from TBL_TEST1delete TBL_TEST1insert TBL_TEST2 values (1, '1', 'male ', '') select * from TBL_TEST2delete TBL_TEST2

 

Technorati label: T-SQL