Database Review ⑧

Database Review ⑧

June 18, 2016

21:23

Main Integrity Constraints Entity integrity constraints

1. various constraints

Keys

Foreign-key FOREIGN KEY referential integrity constraints

value-based Constraints Value Constraint

tuple-based Constraints tuple constraints

Assertions Assertion

2.Key constrains

Primary Key:

CREATE TABLE Branch (

Bname CHAR (PRIMARY KEY),

Bcity CHAR (20),

Assets INT);

Or

CREATE TABLE depositor (

CNAME CHAR (15),

Acct_no CHAR (5),

PRIMARY KEY (CNAME, acct_no));

Candidate Keys:

CREATE TABLE Customer (

SSN CHAR (9) PRIMARY KEY,

CNAME CHAR (15),

Address CHAR (30),

City CHAR (10),

UNIQUE (CNAME, address, city);

3.Attribute Constraints

4.Foreign Keys foreign key

What is the difference between a primary key, a foreign key, and an index?

 	Primary key	foreign key	Index
Definition:	Uniquely identifies a record and cannot have duplicate , not allowed to be empty	Table foreign key is the primary key of another table, foreign key can have duplicate, can be null	This field has no duplicate values, but can have a null value
:	To ensure data integrity	used to establish contact with other tables	is the speed at which query ordering is increased
Number:	Primary key can only have one	A table can have more than one foreign key	A table can have multiple unique indexes

1. A foreign key refers to a primary key that requires another table, or a candidate key.

2, the foreign key is created after the index is not automatically created, this is a developer to consider whether the relevant index on the external key can be obtained to improve the efficiency of the query

3, by default, if you insert a non-existent foreign key value in the reference table or delete a referenced data in the main table, the database will error.

5.Global constraint:assertions Global Limit Assertion

CREATE Assertion <name>

CHECK (<condition>);

6.Trigger Trigger

Grammar:

DELIMITER |

CREATE TRIGGER ' <databaseName> '. ' <triggerName> '

< [before | After] > < [INSERT | UPDATE | DELETE] >

On [dbo]<tablename>//dbo represents the owner of the table

For each ROW

BEGIN

--do something

END |

A trigger (trigger) is a method that SQL Server provides to programmers and data analysts to ensure data integrity, which is a special stored procedure related to table events, which is executed either by a program call or by a manual startup, but by an event, such as when a table is manipulated ( Insert,delete, update) will be activated when it executes. Triggers are often used to enforce data integrity constraints, business rules, and so on.

DML triggers

When the data in a table in a database changes, including insert,update,delete arbitrary action, the trigger executes automatically if we write a corresponding DML trigger on the table. The primary role of DML triggers is to enforce business rules, as well as extend SQL Server constraints, default values, and so on. Because we know that a constraint can only constrain data in the same table, and the trigger executes arbitrary SQL commands.

DDL triggers

It is a new trigger for SQL Server2005, which is mainly used to audit and standardize operations on the structure of tables, triggers, views, etc. in the database. For example, in modifying tables, modifying columns, adding tables, adding columns, and so on. It executes when the database structure changes, we mainly use it to record the database modification process, as well as restrict the programmer to modify the database, such as not allowed to delete some of the specified table.

Example:

When inserting:

Create Trigger Tri_insert

On student

For insert

As

Declare @student_idchar (10)

Select @student_id =s.student_id from students

INNER JOIN Insertedion s.student_id=i.student_id

If @student_id = ' 0000000001 '

Begin

RAISERROR (' Can't insert 1 's school number! '), 16,8

Rollbacktran

End

Go

When updating:

Create Trigger Tri_update

On student

For update

As

If Update (STUDENT_ID)

Begin

RAISERROR (' School number cannot be modified! '), 16,8

Rollbacktran

End

Go

When deleted:

Create Trigger Tri_delete

On student

For delete

As

Declare @student_idvarchar (10)

Select @student_id =student_id from deleted

If @student_id = ' admin '

Begin

RAISERROR (' Error ', 16,8)

Rollbacktran

End

Database Review ⑧