外键定义中的几点说明:
1、1、当主表(主键表)有列定义为Primary Key时,在定义子表(外键表)的外键约束时,关键字References后可以只指定主表名,忽略列名!(主表(主键表)列仅指定Unique约束,则该说明不适用)
2、 2、当定义外键约束时,若有使用到Foreign Key关键字,则其随后的的列名必须带上括号!
3、 3、关键字Foreign Key,只有在定义表级外键约束时才有用到。
4、 4、若需要指定约束名(可选),则使用Constraint foreign_key_name。表级外键约束,放置在Foreign key关键字之前,行级外键约束放置在References关键字之前。
[@more@]外键定义的一些细节
2007.11.11
外键定义中的几点说明:
1、1、当主表(主键表)有列定义为Primary Key时,在定义子表(外键表)的外键约束时,关键字References后可以只指定主表名,忽略列名!(主表(主键表)列仅指定Unique约束,则该说明不适用)
2、2、当定义外键约束时,若有使用到Foreign Key关键字,则其随后的的列名必须带上括号!
3、3、关键字Foreign Key,只有在定义表级外键约束时才有用到。
4、 4、若需要指定约束名(可选),则使用Constraint foreign_key_name。表级外键约束,放置在Foreign key关键字之前,行级外键约束放置在References关键字之前。
主表(主键表):test_a
子表(外键表):test_b
表级外键约束
--------------------------------------------------------------------------------------------------------
SQL> Create table test_a(id int primary key);
Table created.
SQL> Create table test_b(num int, constraint fk_ab foreign key (num) references test_a(id));
Table created.
SQL> Create table test_b(num int, constraint fk_ab foreign key (num) references test_a);
Table created.
SQL> Create table test_b(num int, foreign key (num) references test_a(id));
Table created.
SQL> Create table test_b(num int, foreign key (num) references test_a);
Table created.
---------------------------------------------------------------------------------------------------------
行级外键约束
--------------------------------------------------------------------------------------------------------
SQL> Create table test_a(id int primary key);
Table created.
SQL> Create table test_b(num int references test_a(id));
Table created.
SQL> Create table test_b(num int references test_a);
Table created.
SQL> Create table test_b(num int constraint fk_ab references test_a(id));
Table created.
SQL> Create table test_b(num int constraint fk_ab references test_a);
Table created.
-------------------------------------------------------------------------------------------------------
使用Unique约束,不指定为Primary Key时,说明1的测试:
-------------------------------------------------------------------------------------------------------
SQL> drop table test_a cascade constraints;
Table dropped.
SQL> Create table test_a(id int unique);
Table created.
SQL> Create table test_b(num int, constraint fk_ab foreign key (num) references test_a);
Create table test_b(num int, constraint fk_ab foreign key (num) references test_a)
*
ERROR at line 1:
ORA-02268: referenced table does not have a primary key
SQL> Create table test_b(num int, constraint fk_ab foreign key (num) references test_a(id));
Table created.
-------------------------------------------------------------------------------------------------------
说明2的测试:
-------------------------------------------------------------------------------------------------------
SQL> Create table test_a(id int primary key);
Table created.
SQL> Create table test_b(num int, constraint fk_ab foreign key (num) references test_a(id));
Table created.
SQL> drop table test_b;
Table dropped.
SQL> Create table test_b(num int, constraint fk_ab foreign key num references test_a(id));
Create table test_b(num int, constraint fk_ab foreign key num references test_a(id))
*
ERROR at line 1:
ORA-00906: missing left parenthesis
-------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7988176/viewspace-981778/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7988176/viewspace-981778/