Re: [suse-linux-uk-schools] MIS, databases
On various dates various people said:
<mbrown@fensystems.co.uk> PostgreSQL is probably a better choice ...........
<rjg@ateb.co.uk> I'll second the choice of PostgreSQL
and so on. Now, after all this praise, I installed PostgreSQL and started mucking around with it. I created a table, put data in it and even did a query! I can't figure out how to get an ODBC connection yet to my NT box and I can't find a download source for the mpsql (?) client to run on windows. I thought I might ask those knowledgeable folks on the mailing list. Nevertheless, I really thought I was going somewhere until I discover that PostgreSQL does not support foreign keys. I find this astonishing. Isn't this the whole point of databases? I use a tlkp to prefix lookup tables. If I create a (simplified) table of ethnic groups : CREATE TABLE tlkpEthnic ( ethnic_id int NOT NULL , ethnic_descriptionl varchar (50) NULL , CONSTRAINT pk_tlkpEthnic PRIMARY KEY CLUSTERED ( ethnic_id ) ) and then go on to create a (hugely simplified) table of pupils CREATE TABLE dbo.tblPupil ( pupil_id int IDENTITY (1, 1) NOT NULL , name_first varchar (50) NULL, name_second varchar (50) NULL , ethnic_id int NOT NULL, CONSTRAINT fk_tblPupil_ethnic FOREIGN KEY ( ethnic_id) REFERENCES dbo.tlkpEthnic (ethnic_id), ) I want to require the ethnic_id for every pupil to match an entry in tlkpEthnic. To this end I use REFERENCES. Seemingly PostgreSQL only lets you enforce this through triggers. So, you would have to write triggers for virtually everything. Not to go into preventing deletion from tlkpEthnic where those records are referenced by records in tblPupil. Obviously, PostgreSQL was written by people far more knowledgeable than me, so what am I missing here? Finally,
<alanh@bryngwyn.carmarthen.sch.uk> After a little reading and some downloading of software I have a running PostgreSQL server,............. and access to the PostgreSQL databases on the server via PostODBC and Lotus Approach from Windows systems.
Alan, what extra did you download? Is there not everything I need on the SuSE cd to get ODBC going? regards Richard richard@tortoise.demon.co.uk
On Sat, 12 May 2001 richard@tortoise.demon.co.uk wrote:
<mbrown@fensystems.co.uk> PostgreSQL is probably a better choice ........... <rjg@ateb.co.uk> I'll second the choice of PostgreSQL and so on. Now, after all this praise, I installed PostgreSQL and started mucking around with it. I created a table, put data in it and even did a query! I can't figure out how to get an ODBC connection yet to my NT box and I can't find a download source for the mpsql (?) client to run on windows. I thought I might ask those knowledgeable folks on the mailing list.
If it's *to* your NT box, then I can't help you. I've only ever used PostgreSQL on Linux, for which getting ODBC to work was a simple matter of installing postgresql-odbc and creating a short odbc.ini entry: [OsieResLib] Description = OSIE Resource Library Driver = PostgreSQL Trace = Yes TraceFile = /tmp/sql.log Database = osiereslib Servername = localhost UserName = osieadmin Password = Port = 5432 Protocol = 7.0 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = This could almost certainly have been created using the GUI tools - I didn't try. If you have a Linux box (I presume you do), then why not use it on there instead of trying to get hold of NT versions?
Nevertheless, I really thought I was going somewhere until I discover that PostgreSQL does not support foreign keys. I find this astonishing. Isn't this the whole point of databases?
Extract from PostgreSQL 7.0.2 manual: "REFERENCES Constraint [ CONSTRAINT name ] FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] [ [ NOT ] DEFERRABLE ] [ INITIALLY checktime ] The REFERENCES constraint specifies a rule that a column value is checked against the values of another column. REFERENCES can also be specified as part of a FOREIGN KEY table constraint." Looks like foreign key support to me...
what extra did you download? Is there not everything I need on the SuSE cd to get ODBC going?
I can't speak for SuSE's CD, but you can certainly find everything you need if you do an rpmfind.net search for PostgreSQL. HTH, Michael
Michael, thanks for your post.
Extract from PostgreSQL 7.0.2 manual: "REFERENCES Constraint etc
This is better. I was using PostgreSQL version 6.5 as on the SuSE 6.3 cd. I shall upgrade.
If you have a Linux box (I presume you do), then why not use it on there instead of trying to get hold of NT versions?
I have PostgreSQL running on a linux box. The query tool works fine on that. I can now also see postgresql tables using MS- Access ( to start somewhere) on my NT box. regards Richard richard@tortoise.demon.co.uk
richard@tortoise.demon.co.uk wrote:
Hi Richard, Sorry for the delay in replying - only just come back into work.... I downloaded the PSQL ODBC drivers from the Postgres website and installed them. However, I used Windows '95 with Lotus Approach as the front end. I've never tried it with Windows NT (mainly because I only run Server not workstation)- NT doesn't seem to like Lotus very much! Check www.prostgres.org (I think), the downloads are there and there's a very interesting book in PDF form
Finally,
<alanh@bryngwyn.carmarthen.sch.uk> After a little reading and some downloading of software I have a running PostgreSQL server,............. and access to the PostgreSQL databases on the server via PostODBC and Lotus Approach from Windows systems.
Alan, what extra did you download? Is there not everything I need on the SuSE cd to get ODBC going?
regards Richard richard@tortoise.demon.co.uk
Hope thats of some use to you.. Alan ----------------------------------------------------- Alan Harris Network Manager Bryngwyn School Tel : 01554 750661 Fax : 01554 758255 E-mail: alanh@bryngwyn.carmarthen.sch ----------------------------------------------------- Notes: 1. The contents of this email may be snooped on by interested government parties for unknown purposes! Regulation of Investigatory Powers Act, 2000. 2. The opinions expressed in this email are personal and may not be shared by Bryngwyn School. -----------------------------------------------------
participants (3)
-
Alan Harris -
Michael Brown -
richard＠tortoise.demon.co.uk