Constraints, views, and transactions

Constraints

Check (inspection) constraints allow the insertion of a column's value. It uses the same conditional expression as the WHERE clause.

1 CREATE TABLEPiggy_bank (2IdINTAuto_increment not NULL PRIMARY KEY,3CoinCHAR(1)CHECK(Coininch('P','N','D','Q'))4)

If the inserted value cannot pass the check condition, an error message appears.

View

A view is a table that exists only when you use view in a query. It is called a virtual table because it behaves like a table and can perform operations that are available to the table. Virtual tables are not always stored in the database.

Benefits of the View:

1. The view simplifies complex queries into a single command, resulting in a more relaxed life.

2. Even if you change the database structure all the time, you will not break the dependent table application.

3. Create a view to hide information that the reader does not need to see.

Cases:

 1  create  table   Piggy_bank ( 2  ID int  not  null  auto_increment Span style= "color: #0000ff;" >primary  key  ,  3  coin 1 ) not  null  ,  4  coin_year char  (4  )  5 ) 

CREATE VIEWPb_quarters as SELECT *  fromPiggy_bankWHERECoin= 'Q';CREATE VIEWPb_dimes as SELECT *  fromPiggy_bankWHERECoin='D'  with CHECK OPTION;

INSERT into Pb_quarters VALUES (' ', ' Q ', 1000);

Data is inserted into the table, the view also has
INSERT into Pb_quarters VALUES (' ', 'D', 3000);

Data inserted into the table, not in view

INSERT into Pb_dimes VALUES (' ', ' Q ', 2005);

Because check option generates an error message. Because it is not validated by the WHERE clause

DELETE from pb_quarters WHERE coin = ' N ' or coin = ' P ' or coin = ' D ';

The above query has no effect on the table because he can only find records of the coins whose denomination is ' Q '.

UPDATE pb_quarters SET coin = ' Q ' WHERE coin = ' P ';

The above query has no effect on the table because there is no value for coin = ' P ' in view pb_quarters.

Check OPTION checks each query for insert or delete, which determines whether these queries can be executed according to the WHERE clause in the view.

Updatable views is to change the view of the underlying table, and the content of the view can be updated to include all columns in the table it references that are set to NOT NULL.

Delete view: Drop view pb_dimes;

CHECK CONSTRAINT and views help maintain control when the data is used by more than one person.

Constraints, views, and transactions