could unique key ( not primary ) be a parent for declarative foreign key constraint

Question

Naive question with the answer "No" , I believe, but still would like to ask.

Table_parent

pk_parent_surrogate  
parent_natural_unique_key


Table_child

pk_child_surrogate
child_natural_NOT_unique

Is that true that the only possible declarative relationship among main database vendors is

pk_parent_surrogate ----------<  pk_child_surrogate

and we can't have declarative constraint or foreign key in other words for pair

parent_natural_unique_key -------< child_natural_NOT_unique

Answer 1

My answer here is based on my MS SQL knowledge - although I believe the same answer is correct for ANSI standards as well, i'm not 100% sure...

YES - you CAN do this as long as you've got a unique constraint on the column in your parent table that you want to use as the anchor column for the key.

You can create a FOREIGN KEY constraint as part of the table definition when you create a table. If a table already exists, you can add a FOREIGN KEY constraint, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraints or UNIQUE constraint in another, or the same, table. A table can contain multiple FOREIGN KEY constraints.

And as an example of this sort of key...

use tempdb

CREATE TABLE parent(
    pk int identity primary key, 
    candidate_key int unique not null)

CREATE TABLE child(
    pk int identity primary key, 
    join_key int references parent(candidate_key))

See here for more information.

Answer 2

I too agree with Scott Ivey. Unique keys normally qualify for primary key means we in technical term call it Candidate Key. So its ok to have the unique key column for mapping with foreign key

Answer 3

Try code like this:

create table testunique (id int identity(1,1) primary key, otherid int)
go
create unique index ixOther on testunique(otherid)
go
create table testFK (id int identity(1,1) primary key, someid int)
go
alter table testFK add constraint fkTest foreign key (someid) references testunique(otherid)

Rob