0
CREATE TABLE office(
   office_id    INTEGER  NOT NULL PRIMARY KEY 
  ,office_name VARCHAR(15) NOT NULL
);
CREATE TABLE category(
   category_id    INTEGER  NOT NULL PRIMARY KEY 
  ,category_name VARCHAR(15) NOT NULL
);
CREATE TABLE employee(
   employee_id    INTEGER  NOT NULL
  ,Firstname text NOT NULL
  ,birthdate DATE  NOT NULL
  ,Register  text NOT NULL
  ,Lastname  text NOT NULL
  ,Gender    text NOT NULL
  ,category_id INTEGER  NOT NULL 
  ,office_id INTEGER  NOT NULL 
  ,user_name text NOT NULL
  ,pass_word text NOT NULL,
    foreign key(category_id) references category(category_id),
    foreign key(office_id) references office(office_id),
    primary key(employee_id,category_id,office_id),
    UNIQUE(Firstname)
);

After this i want to create this table but it gives me "there is no unique constraint matching given keys for referenced table "employees" this mistake i don't know how to fix this?

CREATE TABLE employee_phone(
   employee_id INTEGER  NOT NULL
  ,phone INTEGER NOT NULL,
    foreign key(employee_id) references employees(employee_id),
    primary key(employee_id)
);
Gereltod kyro
  • 1
  • 1
  • 3
    The error is *literally* telling you the problem here. What about the error don't you understand and we can try to elaborate. – Thom A May 02 '21 at 10:25
  • Also, don't tag multiple RDBMS unless you're question is really about both. This is clearly not the case here so ensure you retag the one you are using and **only** that one. – Thom A May 02 '21 at 10:26
  • If you *are* using SQL Server, why are you using `text`? `text` has been deprecated since 2005, and I doubt things like your employee's names are going to be 2 billion characters in length... – Thom A May 02 '21 at 10:28
  • i don't know wat i shoud do ,how to fix this error can some one explain this pls – Gereltod kyro May 02 '21 at 10:36
  • A foreign key (FK) must reference to the complete primary key (PK) or a unique (UK). In employee_phone the FK only references a part of the PK, that what the error says. My solution: the model only allows 1 phone per employee. If this is true get rid of employee_phone altogether and move phone to employee table; if multiple phones are allowed consisted a type code, and employee_id+type is PK; employee_id is FK. **Employee PK** is just employee_id. As it stands you can have many, many employees with the same employee_id as long as they are in different category and/or deaprtment. – Belayer May 03 '21 at 00:38

0 Answers0