Re: [SQLObject] question on SQL schemas

[Bud P. B. <bu...@si...>]

On 09 Apr 2003 23:49:32 -0500
Ian Bicking <ia...@co...> wrote:

> As far as UNIQUE, that requires another option to Col.  But that's
> reasonable.

So far, I understand "alternateID" to be equivalent to UNIQUE.  And I
suppose it is possible to have several alternateID columns per table.
So I see no need to introduce a new keyword argument.  

It would be kind of nice to make sure that primary key columns and
unique columns have an index (see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=indexes.html).
This would make lookups much faster...  So at least in backends that
support it, the id column and alternateID columns should trigger the
following SQL clause:

CREATE INDEX tableName_colName_index ON tableName (colName);


Also, it would be nice to be able to control the creation of indices
even if they are not unique.  For that purpose, an additional keyword
argument for Col called "index" could be added.  It could possibly
imply a class method that returns a list of objects (since it is not
unique).  

To cover the multi-column indices and unique constraints, similarly to
_joins, _compositeIndex and _altCompositeID could be added.  This
could trigger the following SQL (from PostgreSQL doc):

CREATE [UNIQUE] INDEX test2_mm_idx ON test2 (major, minor);

CREATE TABLE example (
  a integer,
  b integer,
  c integer,
  UNIQUE (a, c)
);



> For foreign keys, the KeyCol needs to be expanded.  I'm used to MySQL,
> which has very simple (primitive?) joins, where it's entirely implied. 
> Postgres goes further, but I'm not familiar with the syntax.  KeyCol
> needs to be expanded in general, because its arguments should be
> different than Col's arguments.

Here are some examples for the SQL syntax from the PostgreSQL doc.
(Simple cut and paste from
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=ddl-constraints.html#DDL-CONSTRAINTS-FK)

CREATE TABLE products (
  product_no integer PRIMARY KEY,
  name text,
  price numeric
);

CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  product_no integer REFERENCES products (product_no),
  quantity integer
);

and for a multi-field foreign key (that probably isn't needed):

CREATE TABLE t1 (
   a integer PRIMARY KEY,
   b integer,
   c integer,
   FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);


Here also an example for the use of a composite primary key in a table
that breaks up a many-to-many relationship.  I didn't think of this
before and here probably a composite primary key makes a lot of sense.

CREATE TABLE products (
  product_no integer PRIMARY KEY,
  name text,
  price numeric
);
 
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  shipping_address text,
  ...
);
   
CREATE TABLE order_items (
  product_no integer REFERENCES products, 
  order_id integer REFERENCES orders,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
 );


And finally, here is a variation that manages how deletes cascade:

CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

You may want to use this (the CASCADE...) for tables in many-to-many
relationships...

While I'm using PostgreSQL for the first time, and thus am not an
expert, let me know if I can help in any way to get the SQL right.  

best cheers
--bud


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------