Can a unique key ( not a primary key) be a foreign key to other table?

Question

I have two table students and studentsprofilepic 'username' from students is unique key of the table it is referenced as foreign key for the 'studentsprofilepic' table

the DDL for the tables are

CREATE TABLE students (
  id             NUMBER,
  username       VARCHAR2(30),
  password       VARCHAR2(30),
  firstname      VARCHAR2(30),
  lastname       VARCHAR2(40),
  email          VARCHAR2(300),
  dob            VARCHAR2(20),
  alt_email      VARCHAR2(300),
  street_address VARCHAR2(50),
  address_2      VARCHAR2(50),
  city           VARCHAR2(30),
  state          VARCHAR2(30),
  zip            VARCHAR2(10),
  country        VARCHAR2(60),
  telephone      VARCHAR2(10),
  CONSTRAINT student_id_pk PRIMARY KEY (id),
  CONSTRAINT student_username_uk UNIQUE (username)
);

CREATE TABLE studentsprofilepic (
  id       NUMBER,
  photo_id NUMBER,
  photo    BLOB,

  PRIMARY KEY (photo_id),
  FOREIGN KEY (username) REFERENCES students (username)
);

Suasion

What is your problem exactly? – Voltcoulomb

Answer 1

YES, The foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table:

CREATE TABLE BOOK(
    BNAME VARCHAR2(10)NOT NULL UNIQUE,
    BTYPE VARCHAR2(10));
CREATE TABLE BOOKS_AUTH(
    A_ID INT NOT NULL,
    BNAME_REF VARCHAR2(10) NOT NULL,
    FOREIGN KEY (BNAME_REF) REFERENCES BOOK (BNAME));

SQLFIDDLE DEMO

Augustine

Answer 2

Yes, why not. It is possible to reference a UNIQUE constraint in a FOREIGN KEY.

You could have a Primary key and an Unique key, and you would like to validate both.

Honour

Answer 3

Yes, you can reference a column (or columns) governed by either a primary key constraint or a unique constraint.

The problem with your table "studentsprofilepic" is that your foreign key tries to use the column "studentsprofilepic"."username", but that column doesn't exist.

create table studentsprofilepic(
  id number,
  photo_id number,
  photo blob,
  -- Add the "username" column.
  username varchar2(30) not null,
  primary key (photo_id),
  foreign key (username) references students (username)
);

Also, ask yourself "What's the point of "studentsprofilepic"."id"?" It's not the primary key. It's not a foreign key. It doesn't seem to serve any purpose besides letting you say, "Hey, my table has a column named 'id'!" That's a questionable feature.

Think about adding more not null constraints.

Cindicindie