【数据库原理、编程与性能】Integrity, View, Security

聂溪叠

2023-12-01

1. Integrity Constrains

1.1 Definition

表创建时定义完整性约束
按作用对象分为：表级完整性、列级完整性
按类型分为：实体完整性、参照完整性、用户自定义完整性

1.2 实施机制

实体完整性：主键、唯一索引、非空+唯一
参照完整性：外键、触发器、存储过程
用户自定义完整性：非空、缺省值、取值约束等

1.3 Integrity Constraints in Create Table Statement

1.3.1 Create Table

CREATE TABLE [schema.]tablename
( colname datatype [DEFAULT {default_constant | NULL}]
[col_constr {col_constr. . .}] | table_constr
{ , {colname datatype [DEFAULT {default_constant | NULL}]
[col_constr {col_constr. . .}] | table_constr} . . . } );

eg 1 :Create Table statement for “products”.

create table products (pid char(3) not null,
pname varchar(13), city varchar(20),
quantity integer default 0 ,/*缺省填充0*/
price double precision default 0.0 ,
primary key (pid) );

eg 2 :Create Table statement for “customers”.

create table customers (cid char(4) not null unique ,
cname varchar(13), city varchar(20),
discnt real constraint discnt_max check (discnt<=15.0) );/*约束名为discnt_max，约束内容为discnt<=15.0*/

1.3.2 Column Constraints

{NOT NULL |
[CONSTRAINT constraint_name]
UNIQUE | PRIMARY KEY | CHECK (search_cond)
| REFERENCES tablename [(colname) ] [ON DELETE CASCADE]}

CONSTRAINT constraintname name for each constraint

UNIQUE constrained all the non-null column values in table are unique

PRIMARY KEY specifies a column to be a primary key

A column with PK constraint is implicitly in: NOT NULL + UNIQUE

PRIMARY KEY and NOT NULL can be used together, but the

UNIQUE and PRIMARY KEY cannot both be used for a column.

1.3.3 Table Constraints.

[CONSTRAINT constraint_name] {UNIQUE (colname{, colname…})
| PRIMARY KEY (colname {, colname……})
| CHECK (search_condition)
| FOREIGN KEY (colname {, colname……})
REFERENCES tablename [(colname {, colname……})]
[ON DELETE CASCADE]}

eg: Example7.1.2 Create tables of CAP DataBase.

create table customer ( cid char(4) not null,
cname varchar(13), city char(20),
discnt real constraint discnt_max check (discnt <= 15.0),
primary key ( cid ) ) ;

create table orders ( ordno integer not null, month char(3),
cid char(4) not null , aid char(3) not null,
pid char(3) not null , qty integer not null check(qty >= 0) ,
dollars float default 0.0 check(dollars >= 0.0) ,
primary key (ordno) ,
foreign key (cid) references customers,
foreign key (aid) references agents,
foreign key (pid) references products ) ;

1.4 Alter Table Statement

//change the structure of an existing table.

ALTER TABLE tablename
[ADD ({colname datatype [DEFAULT {default_constant| NULL}]
[col_constr {col_constr…}] | table_constr} {, …})]
[DROP {COLUMN columnname | (columnname {, columnname…})}]
[MODIFY (columnname data-type
[DEFAULT {default_const|NULL}] [[NOT] NULL] {, …})]
[DROP CONSTRAINT constr_name]
[DROP PRIMARY KEY]
[ENABLE and DISABLE clauses for constraints];

eg: 修改orders 表中的month 为日期类型、将ordno 字段改为实型.

思路：

(1) 创建日期型字段order_date, 再取出原month 值拼上“年、日”回填；

(2) 修改ordno 字段的类型

ALTER TABLE orders DROP PRIMARY KEY (ordno),

ADD order_date char(10) default “2018/01/01”,

MODIFY ordno real;

UPDATE orders set order_date= ……(month) ;

ALTER TABLE orders DROP month ;

1.5 Trigger

1.5.1 Create Trigger Statement

CREATE TRIGGER trigger_name { BEFORE | AFTER }
{ INSERT | DELETE | UPDATE [OF columnname {,columnname…}]}
ON tablename [REFERENCING corr_name_def {,corr_name-def…}]
[FOR EACH ROW | FOR EACH STATEMENT]
[WHEN (search-condition)]
statement – (single action)
| BEGIN statement; {statement;…} END – (multiple actions)

“corr_name_def” defines a correlation name follows:
{OLD [ROW] [AS] oldrow_corr_name
| NEW [ROW] [AS] newrow_corr_name
| OLD TABLE [AS] oldtable_corr_name
| NEW TABLE [AS] newtable_corr_name}

eg:Use an ORACLE trigger to check the DISCNT value of a new customers does not exceed 15.0 .

CREATE TRIGGER discnt_max AFTER INSERT ON customers
REFERENCING new as x FOR EACH ROW
WHEN ( x.discnt > 15.0)
begin
raise_error(-20003, “ invalid discount on insert ” );
end;

1.5.2 Drop Trigger statement

DROP TRIGGER trigger_name;

1.5.3 Trigger executed

{INSERT | DELETE | UPDATE [of colname {, colname . . .}]}

1.5.4 Two ways to realize the Referential Integrity

(1) Using a Foreign Key

(2) Using a Trigger

2. Creating Views

View is a result from a Subquery, it is a virtual table that no data store in it.

（视图是子表的查询结果，它是一个没有数据存储的虚表）

View has its own name and can be treated as if it were a base table.

2.1 Create Views

CREATE VIEW view_name [(colname {,colname…})] AS subquery [WITH CHECK OPTION];

eg 1 :Create a view “agentorders” that extends the rows of“orders” table to include all information about the agent taking the order.

Create view agentorders (ordno, month, cid, aid, pid, qty, charge,
aname, acity, percent) /* must be named */
as select o.ordno, o.month, o.cid, o.aid, o.pid, o.qty, o.dollars,
a.aname, a.city, a.percent from orders o, agents a
where o.aid = a.aid;

eg 2 :Create a view “acorders” that gives all order information and names of the agent and customers involved in order.

Defined new view from “agentorders” in Example 7.2.1. ( 在视图之上定义视图)

create view acorders (ordno, month, cid, aid, pid, qty, dollars, aname, cname)
as select ordno, month, ao.cid as cid, aid, pid, qty, charge, aname, cname
from agentorders ao, customers c where ao.cid = c.cid;

2.2 Drop Views

DROP {TABLE tablename | VIEW viewname} {CASCADE | RESTRICT};

In ORACLE with [CASCADE], means constraints referring to the table (as Foreign-Key) are dropped. If there is [RESTRICT], then Drop fails.

3. Security & Privileges

3.1 Grant (授予权限)

GRANT { ALL PRIVILEGES | privilege {, privilege…}}
ON [TABLE] tablename | viewname
TO {PUBLIC | user-name {,user-name…}} [WITH GRANT OPTION]

eg:Grant select, update, insert , but not to delete to “tom” on table orders. Then give “tom” authorization for all operations on products.

GRANT select, update, insert ON orders to tom;
GRANT all privileges ON products to tom;

3.2 Revoke (撤销权限)

REVOKE {ALL PRIVILEGES | priv {, priv…}} on tablename | viewname
FROM { PUBLIC | user {, user…} } [CASCADE |RESTRICT];