1

I have got 4 tables named Blood_Bank,Consumer,Employee and Donor.Blood_Bank has SB as primary key which is a dummy attribute.I did not take bank_id as primary key since it can be repeated.To identify each repeated values,I have added one more column called SB.I took bank_id(neither primary nor unique)as the foreign key.These are my tables:

CREATE TABLE Blood_Bank(
    SB varchar(10) PRIMARY KEY,
    bank_id varchar(10) UNIQUE,
    bank_name char(25) NOT NULL,
    bank_add varchar(50) NOT NULL,
    bank_phone varchar(12) NOT NULL,
    b_id varchar(5) NOT NULL,
    b_grp varchar(5) NOT NULL,
    b_quantity varchar(4)NOT NULL,
    b_RHfactor varchar(5)NOT NULL);


CREATE TABLE Consumer(
    c_id varchar(20)PRIMARY KEY,
    c_name char(20) NOT NULL,
    c_add char(50) NOT NULL,
    c_blood varchar(10) NOT NULL,
    c_phone varchar(12) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));


CREATE TABLE Employee(
    emp_id int PRIMARY KEY,
    emp_name varchar(30) NOT NULL,
    emp_dept varchar(30) NOT NULL,
    emp_salary varchar(30) NOT NULL,
    emp_desgn varchar(40) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

CREATE TABLE Donor(
    d_id varchar(20)PRIMARY KEY,
    d_name char(20)NOT NULL,
    d_blood varchar(15)NOT NULL,
    d_phone varchar(20) NOT NULL,
    d_gender char(20) NOT NULL,
    d_age varchar(15) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

But,it is giving me the following error for the tables Consumer,Employee and Donor.

ERROR: there is no unique constraint matching given keys for referenced table "blood_bank".Please help.

  • 3
    If Blood_Bank.bank_id "can be repeated", how can it be a UNIQUE key? And, you say that Blood_Bank.bank_id is not unique, but it is defined as UNIQUE in your table definitions. Which is true? As shown (and as Evan Carroll noted), your code should work as shown at this point; if Blood_bank.bank_id is not actually UNIQUE, then it won't. – RDFozz Jan 24 '18 at 16:51
1

First don't use varchar(x) in PostgreSQL, just use text. Second, all of your char(x) are likely wrong. In SQL, that means store whitespace padding. This is wasteful and a bad idea. You likely do not want char(x). Also, your id columns should probably be int (if you're designing this schema new).

You just need to drop your tables and try again. Your code works fine.

  • What's the benefit of using TEXT over VARCHAR(nn) in PostgreSQL? – Lennart Jan 24 '18 at 16:44
  • 2
    Yes, but when most developers say they want to what they really mean is that they have a totally arbitrary guess that they want to make law, which doesn't usually make sense anyway so most pg developers just use text. If I'm not writing to a spec, I just use text too. – Evan Carroll Jan 24 '18 at 17:03
  • 1
    @Lennart it would be slightly better to use text with a CHECK constraint on ita length than VARCHAR(n). It saves some time when you want to change the value of n. (dropping a constraint and adding another does not need a table rewrite). – ypercubeᵀᴹ Jan 24 '18 at 17:44
  • 1
    @ypercubeᵀᴹ I don't think changing the length of the varchar(n) requires a table rewrite either. Irrc think that's old and bad advice. – Evan Carroll Jan 24 '18 at 17:53
  • 1
    Right. In some cases it needs a table rewrite (when you make the size smaller). Apparently you didn't remember the essence of your answer ;) – ypercubeᵀᴹ Jan 24 '18 at 18:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.