Can a Foreign key reference a combined Unique key?

Question

Is there a way to reference a foreign key to a 2row-unique key?

CREATE TABLE factura( 

num_factura varchar(10), 

ID varchar(10), 

UNIQUE (num_factura,ID)

);

CREATE TABLE detalle_factura(

clave_factura varchar(30),

FOREIGN KEY(clave_factura) references factura(num_factura,ID) 

--I know this is not the right way to do it!


);

I don't know if this makes sense, but if you get it, please help me.

For completeness, this is called a "Composte [foreign] key" (see [this answer](http://stackoverflow.com/a/9780198/1446005) for a syntax example). However, [as Thorsten Kettner suggests](http://stackoverflow.com/a/28559038/1446005), make sure you do need a composite unique constraint, rather that just two standard unique constraints. — RandomSeed, Feb 17 '15 at 13:48
I didn't know it was called that way, thank you very much! This was very useful. — Mónica Luna, Feb 17 '15 at 15:22

score 0 · Answer 1 · answered Feb 17 '15 at 09:50

To start with: If a table contains a column called ID, I expect this to be the unique ID that identifies a record. Often the ID is used as the table's primary key.

You can also have natural keys. As your table is called factura and there is a column num_factura, I would think that this is the unique factura number issued.

If this is so, then UNIQUE (num_factura,ID) would not be helpful, as you want the single attributes to be unique, not the pair of them. With the UNIQUE constraint given thus, you could still have duplicate ID and duplicate num_factura. Use this instead:

CREATE TABLE factura
( 
  num_factura varchar(10) not null, 
  ID varchar(10) not null, 
  UNIQUE KEY (num_factura),
  UNIQUE KEY (ID)
);

(You can also replace on of the UNIQUE KEY keywords with PRIMARY KEY. It doesn't matter which. You would usually use the one which you reference in other tables.)

Now you build your database either ID based or natuaral key based.

Here is an example for the ID concept:

create table factura
( 
  id int not null auto_increment,
  num_factura varchar(10) not null, 
  ...
  primary key (id),
  unique key (num_factura)
);

create table detalle_factura
(
  id int not null auto_increment,
  id_faktura int,
  ...
  primary key (id),
  foreign key(id_faktura) references factura(id)
);

And here is an example for the natural key concept:

create table factura
( 
  num_factura varchar(10) not null, 
  id int not null auto_increment, -- In case you want an additional id for the table (for logging maybe). It is not needed.
  ...
  primary key (num_factura),
  unique key (id) -- In case the table shall have this column.
);

create table detalle_factura
(
  faktura_num varchar(10),
  faktura_sub_num varchar(10),
  id int not null auto_increment, -- Again: Only if you want this additional ID for some purpose.
  ...
  primary key (faktura_num, faktura_sub_num),
  foreign key(faktura_num) references factura(faktura_num),
  unique key (id) -- ditto
);

It's up to you which concept to use. The ID concept has its strength in its technical approach - you can rather easily change the database structure. The natural key concept is strong when it comes to data integrity and easy data access.

Can a Foreign key reference a combined Unique key?

1 Answers1