database 如何在 SQL Server CE（精简版）数据库中创建外键关系？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you create a foreign key relationship in a SQL Server CE (Compact Edition) Database?
提问by Ryan Shripat
Visual Studio 2005 doesn't provide an interface for creating relationships between tables in a SQL Server CE database (I'm using version 3.0) and you can't open a Compact Edition DB using Management Studio as far as I know. Any ideas?
Visual Studio 2005 不提供用于在 SQL Server CE 数据库（我使用的是 3.0 版）中的表之间创建关系的接口，据我所知，您无法使用 Management Studio 打开 Compact Edition DB。有任何想法吗？
回答by Espo
Unfortunately there is currently no designer support (unlike for SQL Server 2005) for building relationships between tables in SQL Server CE. To build relationships you need to use SQL commands such as:
不幸的是，目前没有设计器支持（与 SQL Server 2005 不同）用于在 SQL Server CE 中构建表之间的关系。要建立关系，您需要使用 SQL 命令，例如：
ALTER TABLE Orders
ADD CONSTRAINT FK_Customer_Order
FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
If you are doing CE development, i would recomend this FAQ:
如果您正在进行 CE 开发，我会推荐此常见问题解答：
EDIT: In Visual Studio 2008 this is now possible to do in the GUI by right-clicking on your table.
编辑：在 Visual Studio 2008 中，现在可以通过右键单击您的表在 GUI 中执行此操作。
回答by Ryan Shripat
Visual Studio 2008 doeshave a designer that allows you to add FK's. Just right-click the table... Table Properties, then go to the "Add Relations" section.
Visual Studio 2008确实有一个设计器，允许您添加 FK。只需右键单击表... 表属性，然后转到“添加关系”部分。
回答by Ryan Shripat
You need to create a query (in Visual Studio, right-click on the DB connection -> New Query) and execute the following SQL:
您需要创建一个查询（在 Visual Studio 中，右键单击 DB 连接 -> 新建查询）并执行以下 SQL：
ALTER TABLE tblAlpha
ADD CONSTRAINT MyConstraint FOREIGN KEY (FK_id) REFERENCES
tblGamma(GammaID)
ON UPDATE CASCADE
To verify that your foreign key was created, execute the following SQL:
要验证您的外键是否已创建，请执行以下 SQL：
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
Credit to E Jensen (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532377&SiteID=1)
归功于 E Jensen ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532377&SiteID=1)
回答by Rob
Alan is correct when he says there's designer support. Rhywun is incorrect when he implies you cannot choose the foreign key table. What he means is that in the UI the foreign key table drop down is greyed out - all that means is he has not right clicked on the correct table to add the foreign key to.
艾伦说有设计师支持是正确的。Rhywun 暗示您不能选择外键表是不正确的。他的意思是，在 UI 中，外键表下拉列表是灰色的——这意味着他没有右键单击正确的表来添加外键。
In summary, right click on the foriegn key table and then via the 'Table Properties' > 'Add Relations' option you select the related primary key table.
总之，右键单击外键表，然后通过“表属性”>“添加关系”选项选择相关的主键表。
I've done it numerous times and it works.
我已经做过很多次了，而且很有效。
回答by shahwazir haqmall
create table employee
(
empid int,
empname varchar(40),
designation varchar(30),
hiredate datetime,
Bsalary int,
depno constraint emp_m foreign key references department(depno)
)
We should have an primary key to create foreign key or relationship between two or more table .
我们应该有一个主键来创建两个或多个表之间的外键或关系。
回答by Tharaka
I know it's a "very long time" since this question was first asked. Just in case, if it helps someone,
我知道自从第一次问这个问题以来已经“很长时间”了。以防万一，如果它对某人有帮助，
Adding relationships is well supported by MS via SQL Server Compact Tool Box (https://sqlcetoolbox.codeplex.com/). Just install it, then you would get the option to connect to the Compact Database using the Server Explorer Window. Right click on the primary table , select "Table Properties". You should have the following window, which contains "Add Relations" tab allowing you to add relations.
MS 通过 SQL Server Compact Tool Box ( https://sqlcetoolbox.codeplex.com/)很好地支持添加关系。只需安装它，然后您就可以选择使用服务器资源管理器窗口连接到 Compact Database。右键单击主表，选择“表属性”。您应该有以下窗口，其中包含允许您添加关系的“添加关系”选项卡。
回答by Alex Jolig
Walkthrough: Creating a SQL Server Compact 3.5 Database
演练：创建 SQL Server Compact 3.5 数据库
To create a relationship between the tables created in the previous procedure
在上一过程中创建的表之间创建关系
- In Server Explorer/Database Explorer, expand Tables.
- Right-click the Orders table and then click Table Properties.
- Click Add Relations.
- Type FK_Orders_Customers in the Relation Name box.
- Select CustomerID in the Foreign Key Table Column list.
- Click Add Columns.
- Click Add Relation.
- Click OK to complete the process and create the relationship in the database.
- Click OK again to close the Table Properties dialog box.
- 在服务器资源管理器/数据库资源管理器中，展开表。
- 右键单击 Orders 表，然后单击表属性。
- 单击添加关系。
- 在关系名称框中键入 FK_Orders_Customers。
- 在外键表列列表中选择 CustomerID。
- 单击添加列。
- 单击添加关系。
- 单击“确定”以完成该过程并在数据库中创建关系。
- 再次单击“确定”关闭“表属性”对话框。
