Tips&Tricks系列九：数据库外键的两个细节

今天遇同事询问，内容大概是数据库外键是否可以引用非主键字段，我说只能引用主键，后来试验了一下，并非如此，还可以引用unique key。用了这么久数据库，惭愧惭愧，:(。

 

1）关于外键声明：
写法一：同时指定引用键与被引用键的列名
alter table t2 add constraint fk1 foreign key(x1) references t1(f1);
alter table t2 add constraint fk1 foreign key(x1,x2) references t1(f1,f2);
此种写法要求被引用的必须是主键或者unique key，不能是主键或unique key的一部分或其它字段

ps，unique key指唯一约束：alter table t1 add constraint uq1 unique(f3);
而非唯一索引：create unique index ix1 on t1(f3);

写法二：不指定被引用键的列名（这种写法在一些数据库如oracle中是允许在，在另一些数据库比如mysql中是不允许的）
alter table t2 add constraint fk1 foreign key(x1) references t1;
alter table t2 add constraint fk1 foreign key(x1,x2) references t1;
此种写法默认引用主键，如无主键，直接报错，即便存在unique key也不行。
 （不推荐此种写法，有时候会引起误会，例如表t1包含两个字段col1,col2，主键是col2，表t2中有一个字段叫col1，当你使用第二种外键声明方法将t2.col1声明为外键字段时，实际上它引用的是t1.col2，如果你光看字段名字，往往会以为引用的是t1.col1，造成混乱。）

 

2）另外，对于外键，还有一个细节是，当外键是复合外键（即一个外键包含多个字段）时，在更新或插入数据时，如果其中某一字段为null，则自动跳过此外键检验。

SQL> create table t1(f1 int,f2 int);
SQL> alter table t1 add constraint pk1 primary key(f1,f2);
SQL> insert into t1 values(1,1);

SQL> create table t2(x1 int,x2 int);
SQL> alter table t2 add constraint fk1 foreign key(x1,x2) references t1(f1,f2);
SQL> insert into t2 values(2,null);
1 row created.
SQL> commit;
Commit complete.