09_01-创建和管理表.ppt

Oracle Database 11g: SQL Fundamentals I 10 - * FOREIGN KEY Constraint: Keywords The foreign key is defined in the child table and the table containing the referenced column is the parent table. The foreign key is defined using a combination of the following keywords: FOREIGN KEY is used to define the column in the child table at the table-constraint level. REFERENCES identifies the table and the column in the parent table. ON DELETE CASCADE indicates that when a row in the parent table is deleted, the dependent rows in the child table are also deleted. ON DELETE SET NULL indicates that when a row in the parent table is deleted, the foreign key values are set to null. The default behavior is called the restrict rule, which disallows the update or deletion of referenced data. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table. Oracle Database 11g: SQL Fundamentals I 10 - * CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column. CHECK constraints can be defined at the column level or table level. CREATE TABLE employees (... salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary 0), ... Oracle Database 11g: SQL Fundamentals I 10 - * CREATE TABLE: Example The example in the slide shows the statement that is used to create the EMPLOYEES table in the HR schema. Oracle Database 11g: SQL Fundamentals I 10 - * Violating Constraints When you have constr