外键 Foreign keys

Create Foreign Key Relationships

Create a foreign key relationship in Table Designer

Using SQL Server Management Studio

1. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design.

   The table opens in Table Designer.

2. From the Table Designer menu, click Relationships.   这里需要先右键选中column，然后才会有menu

3. In the Foreign-key Relationships dialog box, click Add.

   The relationship appears in the Selected Relationship list with a system-provided name in the format FK_<tablename>_<tablename>, where tablename is the name of the foreign key table.

4. Click the relationship in the Selected Relationship list.

5. Click Tables and Columns Specification in the grid to the right and click the ellipses (...) to the right of the property.

6. In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.

7. In the grid beneath, choose the columns contributing to the table's primary key. In the adjacent grid cell to the left of each column, choose the corresponding foreign-key column of the foreign-key table.

   Table Designer suggests a name for the relationship. To change this name, edit the contents of the Relationship Name text box.

8. Choose OK to create the relationship.

 

 

 

 

SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

问题

I need to make some changes to a SQL Server 2008 database.

This requires the creation of a new table, and inserting a foreign key in the new table that references the Primary key of an already existing table. So I want to set up a relationship between my new tblTwo, which references the primary key of tblOne.

However when I tried to do this (through SQL Server Management Studio) I got the following error:

The columns in table 'tblOne' do not match an existing primary key or UNIQUE constraint

I'm not really sure what this means, and I was wondering if there was any way around it?

 

回答

It means that the primary key in tblOne hasn't been properly declared - you need to go to tblOne and add the PRIMARY KEY constraint back onto it.

If you're sure that tblOne does have a PRIMARY KEY constraint, then maybe there are multiple tblOne tables in your DB, belonging to different schemas, and your references clause in your FK constraint is picking the wrong one.

If there's a composite key (which your comment would indicate), then you have to include both columns in your foreign key reference also. Note that a table can't have multiple primary keys - but if it has a composite key, you'll see a key symbol next to each column that is part of the primary key.

把B表的一个字段，设置为A表某个字段的外键，前提是B表中的那个字段是主键，或者有唯一性约束