CODEWITHSUNDEEP
sqlpostgresqlforeign-key-relationship
liv a
liv a

Reputation: 3350

creating a table with reference to a non primary key column

I have two tables Users & Stores

Users table primary key is combination of address & phone number (users enroll through a web page and I don't want the same user to enroll twice), the userId column is serial but not a primary key

In Stores table the column of ownerID is it's userID from the users table - but since it's not primary key the reference can not be set (although it's serial)

how can I achieve this result?

Upvotes: 2

Views: 9189

Answers (2)

özüm
özüm

Reputation: 1363

You should make userid primary key, so you could reference it easily in foreign keys. To eliminate duplication of phone and address, you can define a unique constraint or unique index for those columns.

Upvotes: 2

krokodilko
krokodilko

Reputation: 36137

Referenced column need not to be a primary key.

A foreign key can reference columns that either are a primary key OR a unique constraint.

This can be done in this way:

CREATE TABLE Users(
  address varchar(100),
  phone_number varchar(20),
  userid serial,
  constraint pk primary key (address, phone_number ),
  constraint userid_unq unique (userid)
);

create table Stores(
  storeid int primary key,
  ownerID integer,
  constraint b_fk foreign key (ownerID)
    references Users(userid)
);

Upvotes: 2

Related Questions