数据库约束 [英] database constraints

问题描述

如果我有桌子：


创建表格foo

（

a int reference bar（id），

b int引用baz（id）

）


....如何确保一列且只有一列a和b是

非空？它是否合理？

---------------------------（广播结束）----- ----------------------

提示4：不要杀死-9''邮政局长

If I have have the table:

create table foo
(
a int references bar(id),
b int references baz(id)
)

.... how do I make sure one and only one of the columns a and b are
non-null? Is it even reasonable?
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

推荐答案

2004年10月6日星期三上午08:23:11 -0700，Ben写道：

On Wed, Oct 06, 2004 at 08:23:11AM -0700, Ben wrote:

如果我有表：

创建表foo
（一个int引用栏（id），
b int引用baz（id）
）

...如何确保列a和b中的一列只有一个非空？


您可以这样写：


CREATE TABLE foo

（

a参考栏（id），

b INT参考baz（id），

CHECK（（一个IS NULL和b不是NULL）或（一个IS NOT NULL和b IS NULL））

）;

它甚至合理吗？

If I have have the table:

create table foo
(
a int references bar(id),
b int references baz(id)
)

... how do I make sure one and only one of the columns a and b are
non-null?
You could write it like this:

CREATE TABLE foo
(
a INT REFERENCES bar(id),
b INT REFERENCES baz(id),
CHECK((a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL))
);
Is it even reasonable?

什么是'' ？合理" ;）


BTW，id列是一个可怕的名字。最好叫它foo_id。


干杯，

D

-

David Fetter da *** @ fetter.org http://fetter.org/

手机：[phone]手机：[phone]


请记住投票！


---------------------------（播出结束）---- -----------------------

提示3：如果通过Usenet发布/阅读，请发送适当的

subscribe-nomail命令 ma*******@postgresql.org 以便您的

消息可以干净地通过邮件列表

What''s "reasonable?" ;)

BTW, "id" is a terrible name for a column. Better call it foo_id.

Cheers,
D
--
David Fetter da***@fetter.org http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

* Ben< be *** @ silentmedia.com> [2004-10-06 08:23:11 -0700]：

* Ben <be***@silentmedia.com> [2004-10-06 08:23:11 -0700]:

如果我有桌子：

创建表foo
（
一个int引用栏（id），
b int引用baz（id）


...我怎样才能确定一个且只有一个列a和b是否为空？它是否合理？

If I have have the table:

create table foo
(
a int references bar(id),
b int references baz(id)
)

... how do I make sure one and only one of the columns a and b are
non-null? Is it even reasonable?

ALTER TABLE foo ADD CONSTRAINT must_have_a_or_b CHECK（a :: int IS NULL

AND b :: int IS NOT NULL或a :: int IS NOT NULL和b :: int IS NULL）;


-

Steven Klassen - 首席程序员

命令提示符，Inc。 - http://www.commandprompt.com/

PostgreSQL复制&支持服务，[phone]

---------------------------（结束广播）---------------------------

提示4：不要杀人-9 ''postmaster

ALTER TABLE foo ADD CONSTRAINT must_have_a_or_b CHECK (a::int IS NULL
AND b::int IS NOT NULL OR a::int IS NOT NULL AND b::int IS NULL);

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, [phone]

---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster

什么是合理的？ ;）

BTW，id列是一个可怕的名字。最好称之为foo_id。


您好，


我不同意id的想法列是一个可怕的名字。

只对它负面，就是你在进行连接时必须明确你的

声明...例如：

SELECT * FROM foo

JOIN栏上（foo.id = bar.id）


就个人而言，我宁愿看到，也写下来然后：


SELECT * FROM foo

JOIN栏开启（foo_id = bar_id）

此致，


Joshua D. Drake

干杯，
D

What''s "reasonable?" ;)

BTW, "id" is a terrible name for a column. Better call it foo_id.
Hello,

I disagree with the idea that "id" is a terrible name for a column. The
only negative to it, is that you will have to be explicit in your
declarations when doing joins and such... ex:

SELECT * FROM foo
JOIN bar on (foo.id = bar.id)

Personally I would rather see, and write that then:

SELECT * FROM foo
JOIN bar on (foo_id = bar_id)
Sincerely,

Joshua D. Drake

Cheers,
D

-

命令提示符，公司，Mammoth PostgreSQL的主页 - S / ODBC和S / JDBC

Postgresql支持，编程共享主机和专用主机。

+ 1-503-667-4564 - [e-mail] - http://www.commandprompt.com

Mammoth PostgreSQL Replicator。 PostgreSQL的集成复制

---------------------------（广播结束）------ ---------------------

提示9：如果您的
$，计划员将无视您选择索引扫描的愿望b $ b加入列的数据类型不匹配

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match

这篇关于数据库约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！