Constraints on SQL---------tables

Constraints on tables

Prevents the same data from being completely duplicated: PRIMARY KEY constraint (primary key) Unique key constraint (unique), self-growth;

Prevent data from being empty: non-null constraint (notnoll), default value;

Prevent random data: Foreign key, define data type,check;

1 PRIMARY KEY constraint (Primary key constraint) requires the primary key to uniquely differentiate between each row of data, a primary key can be a column, but several columns are combined into a primary key, and are not allowed to be empty. The data in the table is arranged in order by the primary key,

L Statement operation: ALTER TABLE STUINFO add constraint Pk-sruno primary key (Stuno)

L table operation: In the table design interface, on the corresponding column, right-click -" Set Primary key "/" Delete primary key ";

2 Unique constraint (unique constraint) requires the column to be unique, allowed to be empty, but only one null value can appear

L Statement operation: ALTER TABLE STUINFO add constraint unique(stuid)

L table operation: On the table design interface, right-click --"index / key" in the pop-up window, click "Add" in the lower left corner , the right property list, modify type--unique key, Set Column

Example QQ number and QQ Mailbox Relationship

3 Check Constraint (checkconstraint) A column value range formatting restrictions such as age-related restrictions

4 default (defaultconstraint) defaults for a column

Statement operation: Sxex varchar (2) Default (' male '),

Table operations: In the table design interface, select the appropriate column, in the following property panel--default value

Example INSERT into Studente (Sno,sname,sbirthday,class) VALUES (' 201 ', ' Xiao Wang ', ' 1974-06-03 ', ' 95031 ')

5 FOREIGN KEY constraint (foreeign key constraint) is used to establish a relationship between two tables, and the column that references the main table needs to be developed

Iii. Self-growth columns: Identity column,identity column

1. You cannot manually fill in the values. If you are writing an insert statement, the statement should ignore this column

2. self-growing columns are mainly used for integer, Long Integer,Decimal type.

3. do not arbitrarily use the self-growth column as the primary key.

--

Action: In the table design interface, select the appropriate column, in the following property panel--"Identity Specification"-- Select Yes

To prevent some of the filling is not filled:

One, non-null constraints

Cannot be NULL.

Action: Remove the check box in "Allow NULL values" in the table's design interface .

Second, the default value.

If you do not assign a value to the column, it will be filled with the default value.

Action: In the table's design interface, select the appropriate column in the property panel below--default value

To prevent random filling:

One,Check constraints.

Check the data according to a certain rule.

Action: In the table design interface, right-click the appropriate column, selectCHECK Constraint in the popup dialog box, set the name and expression of the constraint.

Second, foreign KEY constraints. The main table, from the table.

The primary table is used to constrain the from table. The foreign key should be built from the table.

Use the primary key of the main table to constrain the foreign key from the table.

The foreign key column from the table cannot be arbitrarily filled, it can only fill in the data that exists in the primary key of the main table.

Once the foreign key relationship is established, the foreign key from the table cannot be filled out, and the primary key column in the main table can not be deleted randomly.

Settings for cascading deletions--sorting

Action: First build the main table, in the table from the design interface, right -click-"relationship", in the Popup dialog box select "Add", in the right attribute list "table and column Specification"

Three, type.

Completeness includes

1 Entity Integrity

Data row cannot exist duplicate

2 Domain Integrity

Implements a limit on the values entered into a particular column

3 Referential Integrity

1 requires that related Hang in the child table also exist in the primary table

2 If the data for the related items in the primary and child tables is established, it must exist in the primary table

3 changes are made to the related item data in the primary table, the data in the child table is also changed accordingly

4 Primary table cannot be deleted before deleting a child table

Iv. Custom Integrity

Create table includes select Field Name data type define whether NULL set default primary key foreign key relationship check constraint, etc. There is no suitable column in the table as the primary key to create the label column.

5 constraints present in SQL

Ways to add constraints: alter table name add constraint constraint name constraint type

Specific constraint description

Constraint name naming rules:

Primary key Pk-stuno

Unique UQ

Default de

Check ck

Foreign key FK

Code add constraint Writing method ( in table stuinfo)

To add a PRIMARY KEY constraint:

Add unique KEY constraint:ALTER TABLE STUINFO add constraint unique(stuid)

Add default constraint key: ALTER TABLE STUINFO add constraint df-stuadddress default(' address unknown ')forstuaddress

Add CHECK constraint:ALTER TABLE stuinfo add constraint ck-stuage check (stuage between)

Add external constraint key: ALTER TABLE stumarks add CONSTRAINT fk_stuno FOREIGN key (Stuno) REFERENCES stuinfo (Stuno)

Constraints on SQL---------tables