0

I have 4 tables (Guardian, Child, Branch and ChildBranch).

Child is weak to Guardian. ChildBranch is the weak associative entity of Child and Branch.

They all have primary keys and data. 

Guardian Primary Key : Email (PK, FK, varChar(100), not null) 
Child Primary Key : Email (PK, FK, varChar(100), not null) 
                    FirstName (PK, varChar(50), not null) 
ChildBranch Primary Key : Email (PK, FK, varChar(100), not null) 
                          FirstName (PK, varChar(50), not null) 
                          Name (PK, varchar(50), not null)

How do I create the foreign key between Child and ChildBranch?

I tried:

ALTER TABLE ChildBranch
ADD FOREIGN KEY (Email)
REFERENCES Child(Email);

But got this error

There are no primary or candidate keys in the referenced table 'Child' that match the referencing column list in the foreign key 'FK__ChildBran__Email__7B5B524B'.

I expect to create the link between the Child and ChildBranch tables.

Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
Smeister
  • 1
  • 3
  • 2
    IF you want to create a foreign key between `ChildBranch` and `Child`, then your referenced table `CHild` **must have** a primary key that **exactly** matches your FK reference. It has to be the same data type, and you cannot reference parts of a composite PK (if you have a composite PK on `Child`). Quite obviously, `Email` alone is **NOT** the PK on your `Child` table .... Show us the **complete** structure of the two tables `Child` and `ChildBranch` so we can help – marc_s Jan 27 '19 at 11:14
  • Can you provide Script of the schema with keys of both tables? – Prashant Pimpale Jan 27 '19 at 11:16
  • Possible duplicate of [There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key](https://stackoverflow.com/questions/17879735/there-are-no-primary-or-candidate-keys-in-the-referenced-table-that-match-the-re) – Prashant Pimpale Jan 27 '19 at 11:19
  • You can see at this link https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-table-constraint-transact-sql?view=sql-server-2017 – Sanpas Jan 27 '19 at 11:22
  • Please **do not** put code samples or sample data into comments - since you cannot format it, it's **extremely hard** to read it.... Instead: **update** your question by editing it to provide that additional information! Thank you. – marc_s Jan 27 '19 at 11:26
  • 1
    Deepest apologies i just started using stack overflow today. – Smeister Jan 27 '19 at 11:33

2 Answers2

0

Since ChildBranch has a composite Primary key of Email and FirstName. The answer should look something like this:

ALTER TABLE ChildBranch ADD FOREIGN KEY (Email, FirstName) REFERENCES Child(Email, FirstName);

Thank you to all those who helped me understand this.

Smeister
  • 1
  • 3
0

CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );

Or

ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Mohammad Shehroz
  • 226
  • 2
  • 11