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