Menu
I am trying to add a foreign key to a table, and it give me the following error:
There are no primary or candidate keys in the referenced table 'tbl_Person' that match the referencing column list in the foreign key 'P_ID'.
I have a tbl_Person, which is defined as:
P_ID INT (Primary Key) f_Name, l_Name the other table is a comments table which is defined as:
C_ID INT, Comments, P_ID (should be the foreign key) Trying to make a one to many relationship table, so when the user add a comment, it is referenced back to him, also, he can add onto the comments without initializing a new comment. Hopefully that makes a little sense.
Ex: Randy Bing enter "I love SQL", his ID is 1, f_Name is Randy, l_Name is Bing, his comments are "I love Sql". His comments should store a unique ID, as well as import his P_ID.
Later on when Randy wants to add onto the comment with the same C_ID where P_ID matches him without creating a new C_ID.
Here is the Code:
ALTER TABLE tbl_Comments ADD CONSTRAINT P_ID FOREIGN KEY (P_ID) REFERENCES tbl_Person(P_ID) Am I close to being on the right track?
866
ALTER TABLE students ADD FOREIGN KEY (student_id) REFERENCES points(id); To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: ALTER TABLE students ADD CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES points(id);
The usual cause are generally a mismatch in the type of the column of the primary table and the foreign table. It can also be a mismatch in the Engine type of two tables i.e. MyISAM or InnoDB. Datatype both columns should have same datatype. int(11) on one table and smallint(5) on another will cause problem.
If we then wanted to enable the foreign key, we could execute the following command: ALTER TABLE inventory CHECK CONSTRAINT fk_inv_product_id; This foreign key example would use the ALTER TABLE statement to enable the constraint called fk_inv_product_id on the inventory table.
This error usually means the datatypes are different between "Comments" and "Person", assuming this is the actual message
The SQL should be this
ALTER TABLE tbl_Comments WITH CHECK ADD CONSTRAINT FK_Comments_Person FOREIGN KEY (P_ID) REFERENCES tbl_Person (P_ID) This matches what you added. So:
94
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
The example creates a foreign key on the column TempID and references the column SalesReasonID in the Sales.SalesReason table.
USE AdventureWorks2012; GO ALTER TABLE Sales.TempSalesReason ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID) REFERENCES Sales.SalesReason (SalesReasonID) ON DELETE CASCADE ON UPDATE CASCADE ; GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
