1

I have two tables, employee and tbl_feedback. What I wanted to do is to add id as a foreign key in tbl_feedback. I already have an id column on my tbl_feedback so I tried altering it using this:

ALTER TABLE tbl_feedback
ADD FOREIGN KEY (id) REFERENCES employee(id);

When I hit go, I only get "Cannot add foreign key constraint". Am I doing it wrong?

tbl_feedback

tbl_feedback

employee employee

DarkBee
  • 16,592
  • 6
  • 46
  • 58
Andre F
  • 33
  • 6
  • Do `employee.id` and `tbl_feedback.id` have the same type? – Philipp Maurer Jan 16 '18 at 12:49
  • @PhilippMaurer Yes, they do have the same type. – Andre F Jan 16 '18 at 12:52
  • 1
    I would bet my lunch that `tbl_feedback.id` is the primary key for `tbl_feedback` table, not a foreign key to the `employee` table. Is there a column such as `tbl_feedback.employee_id` that you should be using instead? – MatBailie Jan 16 '18 at 12:54
  • @MatBailie I have an employee id named as id, my tbl_feedback id is also named id. Is that wrong? Sorry I'm new to sql – Andre F Jan 16 '18 at 12:56
  • 1
    Please supply the full table structure *(DDL)* for both tables. – MatBailie Jan 16 '18 at 12:56
  • @MatBailie Added both schema – Andre F Jan 16 '18 at 12:58
  • Do the tables already contain data and if so, is this data compliant with the `FK` you are trying to create? – DarkBee Jan 16 '18 at 13:00
  • [This question](https://stackoverflow.com/questions/10982992/is-it-fine-to-have-foreign-key-as-primary-key) might help you. – Philipp Maurer Jan 16 '18 at 13:02
  • @DarkBee I'm not sure if I know what you mean, sorry. – Andre F Jan 16 '18 at 13:03
  • Lets say you have the table animal and animal_type with types 1) Cat and 2) Dog. If there is an animal with type 3 (which does not exists in animal_type), then you can't create a FK from animal towards animal_type because there is a constraint conflict – DarkBee Jan 16 '18 at 13:19
  • @DarkBee I do have id's on both my table tho and I wanted to make id on my tbl_feedback a foreign key. I'm not sure if I answered you right. – Andre F Jan 16 '18 at 13:23
  • 1
    The `id` column in `tbl_feedback` has nothing to do with the `id` column in `employee`. Each of those columns is simply a unique identifier for each row in their respective tables. This means that you need to add *(and populate)* a new column before you are able to apply a foreign key constraint. Are you able to alter these tables and add columns? – MatBailie Jan 16 '18 at 13:24
  • ^@MatBailie is correct, you would need to add something like `employee_id` in the table `tbl_feedback` – DarkBee Jan 16 '18 at 13:26
  • @MatBailie Ohhhhhhh alright i'll try. Bear with me, i'll tell you guys what I will get :) – Andre F Jan 16 '18 at 13:28
  • @DarkBee ^^^^^^ – Andre F Jan 16 '18 at 13:29

1 Answers1

1

Reference table must have a PK and that PK fields should not be NULL So, first modify referenced table if required

For example,

ALTER TABLE TblReference
Alter column refid int NOT NULL

ALTER TABLE TblReference
ADD constraint PK_TblReference_RefId primary key (refid)

Then simply add the Foreign Key constraint

ALTER TABLE TblSource
ADD CONSTRAINT FK_TblSource_Id FOREIGN KEY (id)   
    REFERENCES TblReference (refid)
Eralper
  • 6,461
  • 2
  • 21
  • 27
  • Both my pk aren't null. So I did this – Andre F Jan 16 '18 at 13:12
  • `ALTER TABLE tbl_feedback ADD CONSTRAINT tbl_feedback FOREIGN KEY (id) REFERENCES employee (id)` – Andre F Jan 16 '18 at 13:13
  • There is no column in the OP's `tbl_feedback` with a value that refers to the `employee.id` column. ***Until*** that column is added *(an populated)* this answer won't be much use to the OP. – MatBailie Jan 16 '18 at 13:22
  • Of course, if you define a FK constraint the value must exists on the referenced table column. Otherwise, during constraint creation it will fail. So before creating the constraint, you can test if any value in source column is missing in the referred table column – Eralper Jan 16 '18 at 15:42