Restrictions on the external learning memory database

For database constraints, beginners will think that so many statements are similar. Where can I remember? I believe that you will soon remember my blog, but I promise it will never be messy! Not much nonsense!

 

I. Roles and classifications of database Constraints

Primarykey and unique are used to prevent duplicate data (one table) in the table)

Check indicates the definition of the data range of the columns in the table (one table)

Defult indicates the default value in the table.

Relationship between foreignkey tables and tables (two or more tables)

2. There are two different ideas for creating constraints

1. When a subject is created, the constraint is created together.

2. Your table has been created. Add constraints to the table.

 

 

3. I will introduce them one by one.

 

1. primary key constraints

I

① Add constraints to the created table

Note: When adding constraints, you must modify the table (alter [modify]).

Alter table Table1

Add primary key (column name 1, column name 2)

② Create together with the table

Create Table Table1

(Whether the data type of column name 1 is null primary key,

Whether the Data Type of column name 1 is null

)

The above two scenarios set a primary key for a field, followed by multiple columns as the primary key.

Create Table Table1

(

Whether the Data Type of column name 1 is null,

Whether the Data Type of column name 2 is null,

Column name 3: whether the data type is null,

Primary Key (column name 1, column name 2)

)

 

Ii. Check Constraints

 

This is similar to the primary key constraint.

Alter table Table1

Add unique (column name)

 

Iii. Foreign key constraints

 

Many people have never been concerned about the foreign key constraint. What is the foreign key? Which table does the primary key belong? What table is the foreign key in? When I write with a T-SQL statement, is the master table in front or the external table in front? And so on!

Next I will tell you how to get this foreign key.

In fact, there is a master-slave relationship between the primary and Foreign keys, just like their names. A master table, a slave table, and a master table are set with primary keys, the foreign key is set from the table. When the data in the primary key table changes, the data in the foreign key table changes immediately.

I. Add a primary key to an existing table (either add or modify)

Alter table Table1

Add froeign key (column name 1) References Table2 (column name 2)

Ii. Create a foreign key together when creating a table

Create Table Table1

(Column name 4: whether the data type is null primary key,

Column name 3: whether the data type is null,

Column name 1 data type references Table2 (column name 2)

)

 

Note:

Table 1 foreign key table

Table 2 primary key table

Column name 1 is the primary table column

Column name 2 is a foreign key column

The most common practice is that the fields in the columns of the primary table and the foreign key table are the same.

Generally, IDS can be used as primary keys. This is the interaction between the two.

 

Iv. Check Constraints

 

It is also based on the constraints in a table and limits the reference relationships between the columns in the table that directly love your data.

I. Add constraints to a created table

Alter tabel Table1

Add constraint name check (column name restrictions on columns)

Most of the data can be found in the range, such as age, height, and weight.

 

5. Default default Constraints

 

He is a constraint on a certain value, and he is the only sentence associated with the word "".

Add a default constraint to the table

Alter tabletable1

Add default 'default' for column name

 

6. Delete Constraints

 

The deletion constraint is also modified for the table:

Alter table Table1

Drop constraint name

 

Note: The above are some constraints I understand. It's not hard to remember them.

The names starting with 1 are generally alter table or create table.

2. Add constraints to an existing table using alter

3. Create a table

4. All their keywords:

Primary key constraint: Primary Key

Uniqueness constraint: unique

Foreign key constraint: froeign key .... References (reference )..... [Relationship between two tables, which is a reference to each other]

Query constraints :..... Constraint (constraint )....... Check [query fields and restrict fields]

Default constraint :....... Default ............. For ()....... Assign the default value to the field

 

This blog is over. I don't know what you think. If you have something hard to understand, or if I have something wrong, I hope you can point it out! I am also a learner and hope to help you!