Adding Foreign Key, SQL SERVER 2008

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 tabl开发者_如何学Ce 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?

---

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:

• check datatypes are both int
• ensure P_ID is primary key on tbl_Person
• (Edit, Dec 2011) collation and length must be the same for varchar columns too

---

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

---

the name of your constraint, p_id, clashes with the name of the p_id column