pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconstraint
4+ messages / 4 participants
[nested] [flat]
* constraint
@ 2000-05-29 17:22 Bernie Huang <bernie.huang@ec.gc.ca>
2000-05-29 20:59 ` Re: constraint Jan Wieck <JanWieck@t-online.de>
0 siblings, 1 reply; 4+ messages in thread
From: Bernie Huang @ 2000-05-29 17:22 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am a newbie to SQL. Could someone please explain why do I need a
contraint, and how do I use it in SQL? An example is needed. Thanks
- Bernie
Attachments:
bernie.huang.vcf (text/x-vcard, 314B) download
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:bernie.huang@ec.gc.ca
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard
* Re: constraint
2000-05-29 17:22 constraint Bernie Huang <bernie.huang@ec.gc.ca>
@ 2000-05-29 20:59 ` Jan Wieck <JanWieck@t-online.de>
0 siblings, 0 replies; 4+ messages in thread
From: JanWieck@t-online.de @ 2000-05-29 20:59 UTC (permalink / raw)
To: Bernie Huang <bernie.huang@ec.gc.ca>; +Cc: pgsql-sql
Bernie Huang wrote:
> Hi,
>
> I am a newbie to SQL. Could someone please explain why do I need a
> contraint, and how do I use it in SQL? An example is needed. Thanks
Constraints are used to have the database engine ensure the
integrity of the data. It'l like teaching the database itself
about "what makes sense". This can be various things, like
- UNIQUE constraint
In a combination of columns no duplicate entries should
ever occur, so that a select with an exact key will give 0
or 1 rows as result, never more.
- EXPRESSION constraint
Restricts the possible values of a row to a given
expression. For example a list of fixed values for one
column or something like "used_pct + unused_pct = 100".
- NOT NULL constraint
The value for a specified attribute is not allowed to be
the SQL NULL value. NULL in SQL terms means "unknown", and
you cannot tell if "unknown" is equal to "anything" - not
even to another "unknown". Thus, the expression NULL =
NULL must return FALSE. To avoid the problems, that might
arise from this SQL meaning of NULL, you tell that the
application must allways provide a value for this
attribute.
- FOREIGN KEY constraint
Restricts the possible values of a combination of
attributes to existing values that appear in another
table.
Any attempt to modify the data in a way, so that the end
result would violate a given constraint, results in a
transaction abort and finally in a rollback operation, so no
application will ever be able to leave the database in a
physically OK, but logically inconsistent state. The big
benefit is, that if an application programmer forgets about,
the database wouldn't let the errorneous action happen.
Think of a database containing users and discussion forums. A
users login name must be unique, because it's the key you use
to identify the user. So you setup a UNIQUE constraint, and
the database will ensure it is.
Also you have different user types (ADMIN, USER and GUEST).
Your application might behave a little weired if a user type
"FOO" occurs, so tell the database to never accept that value
for this column.
Next you only want to allow a user to be removed, if all his
discussion forum articles are expired and removed. You setup
a FOREIGN KEY constraint, so that noone can store an article
with a user that doesn't exist, and noone can delete a user
that still has articles referencing him.
Oh - if you change the name of a user, you don't want to be
able to forget to change all the references in the articles?
Tell the FOREIGN KEY constraint by specifying ON UPDATE
CASCADE, and the database will automatically change all
articles to the new user name.
Of course, an application can take care of all these "logical
dependancies" of the data. But especially in the case of
UNIQUE and FOREIGN KEY, this sounds much simpler than it is
in concurrent reality. For example in a WEB application,
implemented by tens of CGI scripts, it's hard to never forget
about a single check. It's easier to add another ERROR check
to a CGI later (because some user complained about a
scripting error due to a not catched SQL execution that
failed), than to fix logically inconsistent data that is
already inside of the database.
Good application development has the following, general
steps:
1. Describe the problem to be solved in plain text, as
detailed as possible.
2. Define what information the application needs to solve
the problem.
3. Based on 2. develop the data layout, including all
dependancies (constraints) of the information, the
application has to deal with. This can be done with some
boxes, comments and arrows on a sheet of paper (A0 - one
square meter - is a sheet too).
4. Convert the result of step 3. into a DB schema in SQL
syntax, including all the comments and arrows converted
into CONTRAINT clauses.
5. Hack down a prototype, show it to a possible user (your
actual customer) and iterate back from 5. or 2. until he
buys the result.
Yes, that's really the way of good application development.
The key here is, that however buggy your application is, as
long as the database schema is good you run a very little
risk that the application corrupts the data. It might crash,
try things it shouldn't and fail with cryptic error messages,
fall over it's own feet and whatnot. But all data that
remains after any successful transaction is consistent and
makes sense, according to point 3. above.
A relational database management system (RDBMS) isn't a
stupid byte container with a powerful search engine layerd on
top of it. If you just create a bunch of tables and have your
application doing it all, you abuse it as such. The important
words in RDBMS are RELATIONAL and MANAGEMENT. So tell a real
RDBMS "what" it manages, and it does.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
* constraint
@ 2002-07-02 20:07 Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
2002-07-03 07:02 ` Re: constraint Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Ricardo Javier Aranibar León @ 2002-07-02 20:07 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I need your help.
How I can delete or DROP a constraint?
I use POSTGRESQL 7.0.3
_________________________________________________________________
MSN. Más Útil cada Día. http://www.msn.es/intmap/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: constraint
2002-07-02 20:07 constraint Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
@ 2002-07-03 07:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-07-03 07:02 UTC (permalink / raw)
To: Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>; pgsql-sql
Hi Ricardo,
I assume you're talking about foreign key constraints?
Dropping a constraint is a real pain in all versions of Postgres up to and
including 7.2.1.
You will need to manually drop the RI trigger on the child table and the two
triggers on the parent table.
Techdocs has some information on doing this here:
http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php
Chris
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Ricardo Javier
> Aranibar León
> Sent: Wednesday, 3 July 2002 4:08 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] constraint
>
>
>
>
> Hi list,
> I need your help.
> How I can delete or DROP a constraint?
> I use POSTGRESQL 7.0.3
>
> _________________________________________________________________
> MSN. Más Útil cada Día. http://www.msn.es/intmap/
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-03 07:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-29 17:22 constraint Bernie Huang <bernie.huang@ec.gc.ca>
2000-05-29 20:59 ` Jan Wieck <JanWieck@t-online.de>
2002-07-02 20:07 constraint Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
2002-07-03 07:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox