RE: Table/Column Constraints

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > * I assume that the command is supposed to allow the dropping of unique,
> > primary, foreign key and check constraints?  Should 'not null'
> constraints
> > also be included here?

OK, I have just checked the SQL standard thingy for DROP CONSTRAINT, and it
seems that this is the syntax:

ALTER TABLE <table name> DROP CONSTRAINT <constraint name> <CASCADE |
RESTRICT>

I can't find out what CASCADE and RESTRICE mean?

I presume that CASCADE means that if you're trying to remove a primary key
that is referenced by some other foreign keys, all those foreign keys should
also be dropped.  However, if neither is specified, should it fail?  Or
should it produce an error?  And what on Earth does RESTRICT mean?

Also - given that the correct definition of a foreign key is that is is a
non-key attribute that refers to a primary key in another relation - would
it be really bad behaviour to _not_ drop any referring foreign keys?

> > * Unique constraints are implemented as indicies, so dropping a unique
> > constraint maps to dropping the relevant index.
> > * Not null constraints.  This seems to be a 'for completeness'
> constraint -
> > I presume it's implemented as part of the attribute definition?
>
> AFAIR it's just a bool in the pg_attribute row for the column.

My question then is - if someone adds it as a named attribute, where is its
name stored?

> > Also - is there some good reason why this hasn't been
> implemented yet?  Is
> > there some subtle reason, or is it just that no-one's bothered?
>
> I think no one's got round to it; attention has focused on DROP COLUMN,
> which is a great deal harder.  If you feel like working on DROP
> CONSTRAINT, go for it...

I have a couple of reasons for wanting to work on it and that's that I've
come from a MySQL (*gasp*) background and I've fallen in love with
Postgres's coolness.  However, I also love the admin tool 'phpMyAdmin'.
'phpPgAdmin' is the Postgres equivalent - however it lacks convenience and
many features because various sql commands aren't implemented by Postgress.
I believe that wider use of postgres would be greatly enhanced if phpPgAdmin
had all the features of phpMyAdmin - it would make it a lot easier for me to
convert people!  See, if people can't easily drop constraints (and add
constraints) then it discourages people from playing around with them, and
really learning the advanced features of postgres.

Chris