postgresql pgAdmin 中的主键和外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9726535/
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
Primary & Foreign Keys in pgAdmin
提问by Mr Teeth
I was wondering can some give me an explanation on how to assign primary and foreign keys in pgAdmin?
我想知道有人可以解释一下如何在 pgAdmin 中分配主键和外键吗？
I can't find any information online.
我在网上找不到任何信息。
For example...I've got a Student table with all their details (address, d.o.b. and etc.). I'm going to add a student_number to the table and make it a primary key.
例如...我有一个包含所有详细信息（地址、dob 等）的 Student 表。我将向表中添加一个 student_number 并将其设为主键。
I just want to know how do I do that using pgAdmin? And if you may be kind to explain give me further information on using Primary Keys in postgreSQL (and pgAdmin). The same case with the foreign keys.
我只想知道如何使用 pgAdmin 做到这一点？如果您愿意解释一下，请提供有关在 postgreSQL（和 pgAdmin）中使用主键的更多信息。外键的情况相同。
采纳答案by Erwin Brandstetter
There is no option in pgAdmin to add a column to an existing table and make it the primary key at the same time, because this is hardly possible.
pgAdmin 中没有选项可以将列添加到现有表并同时使其成为主键，因为这几乎不可能。
A primary key column needs to hold unique non-null values. Upon adding a column to an existing table, it holds NULL values. So you have to enter unique values before you can add a UNIQUEor PRIMARY KEYconstraint.
主键列需要保存唯一的非空值。将一列添加到现有表后，它会保存 NULL 值。因此，您必须先输入唯一值，然后才能添加UNIQUE或PRIMARY KEY约束。
There is an exceptionto that rule, though: If you add a serialcolumn, unique values are inserted automatically. In this case, you can also define it PRIMARY KEY right away:
但是，该规则有一个例外：如果添加serial列，则会自动插入唯一值。在这种情况下，您也可以立即将其定义为 PRIMARY KEY：
ALTER TABLE student ADD COLUMN student_number serial PRIMARY KEY;
This works in PostgreSQL 9.1. I am not sure it does in older versions, too.
这适用于 PostgreSQL 9.1。我不确定它在旧版本中也是如此。
pgAdmin does not incorporate this special case for serialcolumns in the "New column..." dialog at this time (version 1.14).
pgAdmin 目前没有serial在“新列...”对话框（版本 1.14）中为列合并这种特殊情况。
回答by Victor Barrantes
Yes, there is a way to add Primary & Foreign Keys in pgAdmin.
是的，有一种方法可以在 pgAdmin 中添加主键和外键。
Tested in pgAdmin III Ver.1.16.1 (Windows 7)
在 pgAdmin III Ver.1.16.1 (Windows 7) 中测试
- Select the table you want
- Ctrl+Alt+Enteror right-click / Properties
- Select "Constraints" tab
- At the left-bottom side of the form you will see the option "Primary Key"
- Click add
- Select "Columns" tab
- Select the column you want as a key
- Click add
- 选择你想要的表
- Ctrl+ Alt+Enter或右键单击 / 属性
- 选择“约束”选项卡
- 在表格的左下角，您将看到“主键”选项
- 点击添加
- 选择“列”选项卡
- 选择要作为键的列
- 点击添加
And you are all set.
你已经准备好了。
You can fill more things if you want, but now you know how to get there.
如果您愿意，您可以填充更多东西，但现在您知道如何到达那里。
回答by Thirumal
The below SQL will work
下面的 SQL 将起作用
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name='table_name';
回答by Yadana Chaw
In Pgadmin3,
在 Pgadmin3 中，
- Go to table which you want to add the PK or FK and right click and choose properties.
- Go to constraints tab.
- Choose Primary Key or Foreign Key in drop down list which beside of Add button.
- And than click on add button.
- Go to columns tab.
- Choose the column name in drop down list ,which you want to add .
- Click add button.
Click Ok button.
Hope it will helpful for you !
- 转到要添加 PK 或 FK 的表，然后右键单击并选择属性。
- 转到约束选项卡。
- 在添加按钮旁边的下拉列表中选择主键或外键。
- 然后点击添加按钮。
- 转到列选项卡。
- 在下拉列表中选择要添加的列名称。
- 单击添加按钮。
单击确定按钮。
希望对你有帮助！
