SQL Server to create related constraint implementation methods for databases and datasheets _mssql

This paper analyzes the implementation method of the related constraint of SQL Server creating database and data table. Share to everyone for your reference, specific as follows:

The syntax for creating constraints is as follows:

CREATE DATABASE [test]
on
(name=n ' test ', filename=n ' d:\SQL2kt_Data\test.mdf ', size=3mb,maxsize=unlimited, FILEGROWTH=1MB)
LOG on
(name=n ' Test_log ', filename=n ' d:\SQL2kt_Data\test_log.ldf ', size=1mb,maxsize= 2048mb,filegrowth=10%) Go

noun interpretation (translation):

Constraint

1. Constraints [C][(+on)]
Legal constraints on the company ' s activities
Restrictions on the law of the Company's activities

2. Force; Force [U]
He acted under constraint.
He was forced to take action.

3. Restraint; attitude unnatural [U]
She showed constraint in the presence of the strangers.
She seemed very restrained in front of strangers.

4. Detention [U]

5. Binding (or limiting) things [C]

Clustered

Gather in groups of

--Primary FOREIGN key: Select the column that sets the foreign key, right--relation--table and column specification--click the button with "..."

--Create a table with a primary key, where [Tid]desc, looks like a flashback to add numbers, but it's not, it's normal to add data, but when the data is added, the last data added will be the first to be queried.

CREATE TABLE Dbo.test3 (
 [TID] [int] identity (100,1) not NULL,
 [name] [varchar] (MB),
constraint [Pk_tid] Primary key clustered (
 [TID] desc
)
) on [primary]

--Set FOREIGN key

ALTER TABLE DBO.TEST4 add fkt
 foreign key (TID)
 references (from) dbo.test3 ([Tid]) on UPDATE CASCADE on DELETE Casca DE

--to set a primary key for a table that does not have a primary key set, the primary key field must be non-null.

Copy Code code as follows:

ALTER TABLE DBO.TEST5 with CHECK add constraint pk_id primary key (ID)

--Delete primary key ()

ALTER TABLE TEST5
drop constraint (restricted) pk_id (alias)

--Delete foreign keys

ALTER TABLE test4
drop constraint fkt (alias)

Constraints

--Non-null constraint

ALTER TABLE TEST5
ALTER column name int NOT NULL

--Unique constraint

Establish a unique constraint directly in the table,
Constraint constraint alias unique list name

CREATE TABLE DBO.TEST6 (
 ID int not NULL,
 vname varchar ()
constraint test6_unique unique nonclustered C11/>vname ASC
 )
)

--check constraints

Establish a CHECK constraint

Constraint constraint alias check constraint

Modify

ALTER TABLE TEST6 with
nocheck add constraint test6_check
check (vname!= ' shit ')

--Unload constraints

ALTER TABLE TEST6
drop constraint Test6_check

--Create a modified view

Create VIEW Dbo.view2
as
select * from Dbo.test6 where dbo.test6.id <= 3;

--See results SELECT * FROM DBO.VIEW2
--Delete an attempt to

Drop View Dbo.view2

--Primary FOREIGN key: Select the column that sets the foreign key, right--relation--table and column specification--click the button with "..."

--Create a table with a primary key, where [Tid]desc, looks like a flashback to add numbers, but it's not, it's normal to add data, but when the data is added, the last data added will be the first to be queried.

CREATE TABLE Dbo.test3 (
 [TID] [int] identity (100,1) not NULL,
 [name] [varchar] (MB),
constraint [Pk_tid] Primary key clustered (
 [TID] desc
)
) on [primary]

--Set FOREIGN key

ALTER TABLE DBO.TEST4 add constraint fkt
 foreign key (TID)
 references Dbo.test3 ([Tid]) on UPDATE CASCADE on DELE TE CASCADE

--to set a primary key for a table that does not have a primary key set, the primary key field must be non-null.

Copy Code code as follows:

ALTER TABLE DBO.TEST5 with CHECK add constraint pk_id primary key (ID)

--Delete primary key

ALTER TABLE TEST5
drop constraint pk_id

--Delete foreign keys

ALTER TABLE test4
drop constraint fkt

Constraints

javascript: null sentence
Logical layer Validation: Verify through Java or C #: Login name is correct, uniqueness is usually done here, minimizing the load on the database server
Database validation: Unique constraint, check constraint

--Non-null constraint

ALTER TABLE TEST5
ALTER column name int NOT NULL

--Unique constraint

CREATE TABLE DBO.TEST6 (
 ID int not NULL,
 vname varchar ()
constraint test6_unique unique nonclustered C13/>vname ASC
 )
)

--Create a unique constraint for an existing field

CREATE UNIQUE index index name on table name (field name)

Note: Values cannot be duplicated in a field

--check constraints

ALTER TABLE TEST6 with
nocheck add constraint test6_check
check (vname!= ' shit ')
ALTER TABLE test3 with
n Ocheck add constraint test3_check2
check (tname!= ' shit ' and tname!= ' fuck ' and tname '!= ')

--Unload constraints

ALTER TABLE TEST6
drop constraint Test6_check

--Default constraint

CREATE TABLE test4 (
 tid int,
 pwd varchar () default ' 000000 ' NOT null
)

--Add default constraints to existing fields

Copy Code code as follows:

ALTER TABLE TEST3 add default 0 for Tname1

--Adding bound values

Copy Code code as follows:

EXEC sp_bindefault td, ' Test2.vname '

--Unloading bound values

Copy Code code as follows:

exec sp_unbindefault ' test2.vname '

Add: Constraints in the database

The purpose of the constraint: to ensure the integrity of the data in the table

1. Common Types of constraints:

A PRIMARY KEY constraint (Primary key Constraint): Requires primary key column data to be unique and not allowed to be empty
(b) Unique constraint (unique Constraint): The column is required to be unique, allowed to be empty, but only one null value can appear.
c) Check constraint (check Constraint): A column value range limit, format limit, etc., such as age-related constraints
d Default Constraints (default Constraint): A column defaults, if more boys, the sex defaults to "male"
e) FOREIGN KEY constraint (Foreign key Constraint): For relationships between two tables, you need to specify which column to reference the primary table

2. Format of constraint:

ALTER TABLE name

Add constraint constraint name (name rule: Constraint type _ constraint field) constraint type specific constraint description
3. Examples:

ALTER TABLE Stu
  add constraint Pk_stuno primary key (SNO)--sno number is the primary key
ALTER TABLE Stu
  add constraint uq_stuid un Ique (SID)--sid is the identity card number, and each ID number is the only
ALTER TABLE Stu
  add constraint df_sadess default (' Address unknown ') for Saddress--saddress is the address, the default value is address unknown
ALTER TABLE stu
  add constraint ck_sage Check (sage between)--sage student age, Requires its value to be in between
ALTER TABLE scores
  add constraint Fk_st foreign key (Sno) references Stu (SNO)
-FOREIGN KEY constraint, Primary table Stu connections from table scores, key fields Sno

Creating a constraint between tables is not difficult, but a professional noun needs to remember

I hope this article will help you with your SQL Server database design.