inline and outline

Foreign Key Constraint Example The following statement creates the dept_20 table and defines and enables a foreign key on the department_id column that references the primary key on the department_id column of the departments table:

CREATE TABLE dept_20

   (employee_id     NUMBER(4),

    last_name       VARCHAR2(10),

    job_id          VARCHAR2(9),

    manager_id      NUMBER(4),

    hire_date       DATE,

    salary          NUMBER(7,2),

    commission_pct  NUMBER(7,2),

     department_id   CONSTRAINT fk_deptno

                    REFERENCES departments(department_id) );

The constraint fk_deptno ensures that all departments given for employees in the dept_20 table are present in the departments table. However, employees can have null department numbers, meaning they are not assigned to any department. To ensure that all employees are assigned to a department, you could create a NOT NULL constraint on the department_id column in the dept_20 table in addition to the REFERENCES constraint.

Before you define and enable this constraint, you must define and enable a constraint that designates the department_id column of the departments table as a primary or unique key.

The foreign key constraint definition does not use the FOREIGN KEY clause, because the constraint is defined inline. The data type of the department_id column is not needed, because Oracle automatically assigns to this column the data type of the referenced key.

The constraint definition identifies both the parent table and the columns of the referenced key. Because the referenced key is the primary key of the parent table, the referenced key column names are optional.

Alternatively, you can define this foreign key constraint out of line:

CREATE TABLE dept_20

   (employee_id     NUMBER(4),

    last_name       VARCHAR2(10),

    job_id          VARCHAR2(9),

    manager_id      NUMBER(4),

    hire_date       DATE,

    salary          NUMBER(7,2),

    commission_pct  NUMBER(7,2),

    department_id,

    CONSTRAINT fk_deptno

      FOREIGN  KEY (department_id)

      REFERENCES  departments(department_id) );

The foreign key definitions in both variations of this statement omit the ON DELETE clause, causing Oracle to prevent the deletion of a department if any employee works in that department.

以下是SQL的内联与外联

这个概念一般看书不好理解。其实夜简单。有例子就简单了。

比如：

表A（主表）
cardid username
16 aa
23 bb
25 cc
29 dd
30 ee

表B（子表）
countid cardid score
1 16 34
2 25 300
3 29 1.5

在 cardid 列上联接 A 表和B 表。

分别用内联、外联试试。

内联：
SELECT cardid FROM A INNER JOIN B ON(A.cardid<>B.cardid)
那么这样查询就会交叉地拿A和B去比较，上例来说就是拿『16，23，25，29，30』和16，25，29比。那么显然<>的结果是：23，25，29，30，16，25，29，30。。。不符合我们的要求。因为内联本来就只有找相同的功能，没有找不同的功能。

左外联：
SELECT cardid FROM A LEFT OUTER JOIN B ON (B.cardid=A.cardid) WHEREB.cardid IS NULL
这里会拿左表（A）的所有行去和B比较，上例来说是『16，23，25，29，30』先和16比较，然后再和23比较。它将包括所有A表内容，而对应的B表，符合条件就打印，否则没有的话会为null。所以这样就按要求得到了为null的值，也就是缺少的。

右外联：
SELECT cardid FROM A RIGHT OUTER JOIN B ON (B.cardid=A.cardid)WHERE B.cardid IS NULL
这里会拿右表（B）的所有行去和A比较，上例来说是16，25，29和『16，23，25，29，30』比较。结果只包含B表的所有行。在这里显然不符合要求。

但SELECT cardid FROM B RIGHT OUTER JOIN A ON (B.cardid=A.cardid)WHERE B.cardid IS NULL是对的。事实上右外联都是通过转换为左外联实现的。