SQL ForeignKey Column

Question

If i use a ForeignKey in SQL, does it always have to use all the column of the table I reference from?

So eg.

Table1

subjectID firstname surname email

Table2:

car books Foreignkey(SubjectID)

Can i only use one column as Foreignkey, or do I always have to get all the columns?

Thanks!

Answer 1

You can use only the selected column or a combination of all columns as the foreign key.

Answer 2

A foreign key has to reference a unique key, usually the primary key.

So, if the parent table has a single column in its primary key that is the only column you need to use in the foreign key. If the parent table has a compound primary key (i.e. several columns) then you need all of those columns in the child table.

This is one reason why people tend to avoid using compound primary keys in favour of surrogate keys and unique constraints.

Here is a worked example (using Oracle but it works the same across all flavours of RDBMS). First we create a parent table with a single column primary key and reference it from the child table.

SQL> create table t1 (id number not null, seqno number not null)
  2  /

Table created.

SQL> alter table t1 add constraint t1_pk primary key (id)
  2  /

Table altered.

SQL> create table t2 (id number not null, t1_id number not null, whatever varchar2(10) )
  2  /

Table created.

SQL> alter table t2 add constraint t2_t1_fk foreign key (t1_id)
  2             references t1 (id)
  3  /

Table altered.

SQL>

Simple enough. But if we drop those keys and give T1 a compound primary keys things fall apart ...

SQL> alter table t2 drop constraint  t2_t1_fk
  2  /

Table altered.

SQL> alter table t1 drop constraint  t1_pk
  2  /

Table altered.


SQL> alter table t1 add constraint t1_pk primary key (id, seqno)
  2  /

Table altered.

SQL> alter table t2 add constraint t2_t1_fk foreign key (t1_id)
  2             references t1 (id)
  3  /
           references t1 (id)
                          *
ERROR at line 2:
ORA-02270: no matching unique or primary key for this column-list


SQL> 

We need to add a matching second column to the child table and include it in the foreign key definition:

SQL> alter table t2 add t1_seqno number
  2  /

Table altered.

SQL> alter table t2 add constraint t2_t1_fk foreign key (t1_id, t1_seqno)
  2             references t1 (id, seqno)
  3  /

Table altered.

SQL>

Answer 3

In simple words, the primary key of a table is used as a foreign key in other tables. We don't use all columns of of a table as foreign keys in other tables(this would again lead to redundancy which is again problamatic).

Moreover you can have primary key, unique keys , primary keys + 'some other column' etc as foreign keys in other tables.