SQL Server creates database and data table related constraints implementation method, SQL Server

SQL Server creates database and data table related constraints implementation method, SQL Server

This article analyzes the implementation of constraints related to creating databases and data tables on SQL Server. We will share this with you for your reference. The details are 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

Glossary (translation ):

Constraint

1. constraints; restrictions [C] [(+ on)]
Legal constraints on the company's activities
Legal restrictions on activities of the company

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

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

4. Detention [U]

5. Restrained (or restricted) things [C]

Clustered

Clustering

-- Primary foreign key: select the columns for which the foreign key is set, right-click the column, and choose link> table and column specifications. Then, click the button "...".

-- Create a table with a primary key. [tid] desc indicates that a number is added in reverse mode. Actually, it is normal to add data. However, after adding data, the last added data will be first queried.

create table dbo.test3( [tid] [int] identity(100,1) not null, [name] [varchar](100),constraint [pk_tid] primary key clustered( [tid] desc))on [primary]

-- Set the foreign key

Alter table dbo. test4 add fkt foreign key (tid) references (from) dbo. test3 ([tid]) ON UPDATE CASCADE ON DELETE CASCADE

-- Set a primary key for a table without a primary key. The primary key field must be non-empty.
Copy codeThe Code is as follows: alter table dbo. test5 with check add constraint pk_id primary key (id)

-- Delete primary key ()

Alter table test5drop constraint (Restriction) pk_id (alias)

-- Delete a foreign key

Alter table test4drop constraint fkt (alias)

Constraints

-- Non-empty Constraint

alter table test5alter column name int not null

-- Unique constraint

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

create table dbo.test6( id int not null, vname varchar(20)constraint test6_unique unique nonclustered( vname asc ))

-- Check Constraints

Create check Constraints

Constraint: alias check Constraints

(Modify)

alter table test6with nocheck add constraint test6_checkcheck(vname != 'shit')

-- Uninstall Constraints

alter table test6drop constraint test6_check

-- Create and modify a view

create view dbo.view2asselect * from dbo.test6 where dbo.test6.id <= 3;

-- View the result select * from dbo. view2
-- Delete an attempt

Drop view dbo. view2
 
-- Primary foreign key: select the columns for which the foreign key is set, right-click the column, and choose link> table and column specifications. Then, click the button "...".

-- Create a table with a primary key. [tid] desc indicates that a number is added in reverse mode. Actually, it is normal to add data. However, after adding data, the last added data will be first queried.

create table dbo.test3( [tid] [int] identity(100,1) not null, [name] [varchar](100),constraint [pk_tid] primary key clustered( [tid] desc))on [primary]

-- Set the foreign key

alter table dbo.test4 add constraint fkt foreign key (tid) references dbo.test3([tid]) ON UPDATE CASCADE ON DELETE CASCADE

-- Set a primary key for a table without a primary key. The primary key field must be non-empty.
Copy codeThe Code is as follows: alter table dbo. test5 with check add constraint pk_id primary key (id)
-- Delete a primary key

alter table test5drop constraint pk_id

-- Delete a foreign key

alter table test4drop constraint fkt

Constraints

// Javascript: empty
// Logic layer verification: verify by using java or c #: whether the login name is correct. uniqueness is usually done here to minimize the load on the database server.
// Database verification: unique and check Constraints

-- Non-empty Constraint

alter table test5alter column name int not null

-- Unique constraint

create table dbo.test6( id int not null, vname varchar(20)constraint test6_unique unique nonclustered( vname asc ))

-- Create a unique constraint for an existing field

Create unique iNDEX name ON table name (field name)

Note: existing values in the field cannot be repeated.

-- Check Constraints

alter table test6with nocheck add constraint test6_checkcheck(vname != 'shit')alter table test3with nocheck add constraint test3_check2check(tname != 'shit' and tname != 'fuck' and tname != 'ohyeah')

-- Uninstall Constraints

alter table test6drop constraint test6_check

-- Default Constraint

create table test4( tid int, pwd varchar(20) default '000000' not null)

-- Add default constraints to existing fields
Copy codeThe Code is as follows: alter table test3 add default 0 for tname1
-- Add binding value
Copy codeThe Code is as follows: exec sp_bindefault td, 'test2. vname'
-- Unbind Value
Copy codeThe Code is as follows: exec sp_unbindefault 'test2. vname'

Supplement: Database Constraints

Purpose: To ensure data integrity in a table

1. Common constraints:

A) Primary Key Constraint (Primary Key Constraint): the data in the Primary Key column must be unique and cannot be blank.
B) Unique Constraint (Unique Constraint): This column must be Unique and can be null, but only one null value can appear.
C) Check constraints: A column's value range and format restrictions, such as age constraints
D) Default Constraint (Default Constraint): the Default value of a column. If there are many boys, the Default gender is "male"
E) Foreign Key Constraint (Foreign Key Constraint): used to establish a relationship between two tables. You must specify the columns that reference the primary table.

2. constraint format:

Alter table name

Add constraint name (naming rule: constraint type _ constraint field) constraint type constraints
3. Example:

Alter table stu add constraint pk_stuno primary key (sno) -- The sno student ID is the primary key alter table stu add constraint uq_stuid unique (sid) -- sid is the ID card number, each ID card number is the unique alter table stu add constraint df_sadess default ('address unknown ') for saddress -- saddress is the address, the default value is the address unknown alter table stu add constraint ck_sage check (sage between 15 and 40) -- sage student age, which requires the value to alter table scores add constraint fk_st foreign key (sno) references stu (sno) -- foreign key constraint, master table stu connection slave table scores, keyword segment sno

It is not difficult to create constraints between tables, but you need to remember the terminology of the Major.

I hope this article will help you design SQL Server databases.