Oracle concept 学习 6---foreign key, primary, unique key, check

最新推荐文章于 2024-09-14 19:55:29 发布

cnag5969

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量127

点赞数

文章标签：数据库

unique key: allow null ,but primary key do not allow.

You can only add a column with a NOT NULL constraint if the table does not contain any rows or if you specify a default value.

primary key creates a unique index and a not null constraint.
1.In some cases, as when you create a primary key with a deferrable constraint, the generated index is not unique.
2.If a usable index exists when a primary key constraint is created, then the constraint reuses this index and does not implicitly create a new one.

foreign key

Foreign keys may be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same data types.
The value of foreign keys can match either the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.

例子：
Referenced or Parent Table： department .   column is department.department_id-----Parent Key Primary key of referenced table
Dependent or Child Table:       Employee.       column is employee.department_id ----- it is called foreign key.

3. foreign key 基础上的删除和修改

delete cascades. For example, the deletion of a row in departments causes rows for all employees in this department to be deleted.
delete set null. For example, the deletion of a department row sets the department_id column value to null for employees in this department.

4. foreign key & indexs

As a rule, foreign keys should be indexed.

Prevents a full table lock on the child table. Instead, the database acquires a row lock on the index.
Removes the need for a full table scan of the child table. As an illustration, assume that a user removes the record for department 10 from the departments table. If employees.department_id is not indexed, then the database must scan employees to see if any employees exist in department 10.

ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);

5. constraint status

1.disable/enable    可以约束未来将要添加或者修改的数据
2.validate/novalidate 可以约束已经存在的数据

6.deferrable

INITIALLY IMMEDIATE    在每个语句后开始检查
INITIALLY DEFERRED     commit 之后才开始检查，如果违反， rollback.
ALTER TABLE cust ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;