Postgresql foreign key handling

[angelo]

And the good news is: in the 1.1.1 beta the boolean values appear identical for MSSQL and PGSQL, so that problem is solved.

[mike.griffin]

Okay, I'm sorry for all of the frustration.

First, I'm going to do the TableName + FKName contatenation to avoid collisions (ie, $1 on every table)

Second, I'm strapping on the night vision goggles and going in. I'll single step through it all talking tough, kicking butt, and taking down names until somebody gives the bug up.

This will be working correctly with 1.1.1, it has to.

[angelo]

Hey, don't feel offended please - I'm not that frustrated You can borrow my infrared goggles if you want ....

I'm analyzing the database now - trying to mimic snakess query on MSSQL, to see where that gets me.

[mike.griffin]

I'm going to have to enlist the folks over at PostgreSQL, I don't understand I guess how PostgreSQL can show the foreignkey on the Orders table, and MyGeneration indeed shows it as the primary table, yet you are saying I have it backwards?

I changed your SQL Query to this:

Code: Select all

create table orders
(
  order_id int primary key,
  client_id int,
  description varchar(80)
);

create table client
(
  client_id int primary key references orders,
  name varchar(80)
);

basically reversing who creates the key, now it shows Client as the primary key. Here is the raw sql you can run in PGAdmin III.

Code: Select all

SELECT pfk.relname || '.' || ct.conname as FK_NAME, pn.nspname as PK_TABLE_SCHEMA, pfk.relname as PK_TABLE_NAME,
ct.conkey as PK_COLS, fn.nspname as FK_TABLE_SCHEMA, ffk.relname as FK_TABLE_NAME, ct.confkey as FK_COLS, d.DESCRIPTION,
CAST(CASE ct.confupdtype WHEN 'c' THEN 'CASCADE'
    WHEN 'n' THEN 'SET NULL'
    WHEN 'd' THEN 'SET DEFAULT'
    WHEN 'r' THEN 'RESTRICT'
    WHEN 'a' THEN 'NO ACTION' END
   AS character varying) AS update_rule, 
CAST(CASE ct.confdeltype WHEN 'c' THEN 'CASCADE'
    WHEN 'n' THEN 'SET NULL'
    WHEN 'd' THEN 'SET DEFAULT'
    WHEN 'r' THEN 'RESTRICT'
    WHEN 'a' THEN 'NO ACTION' END
   AS character varying) AS delete_rule
FROM pg_constraint ct
JOIN pg_class pfk on pfk.oid = ct.conrelid
JOIN pg_class ffk on ffk.oid = confrelid
JOIN pg_namespace pn ON pn.oid = pfk.relnamespace
JOIN pg_namespace fn ON fn.oid = pfk.relnamespace
LEFT OUTER JOIN pg_description d ON d.objoid = ct.oid
WHERE contype='f';

I haven't changed it but I suspec that this line:

Code: Select all

JOIN pg_namespace fn ON fn.oid = pfk.relnamespace

should really be

Code: Select all

JOIN pg_namespace fn ON fn.oid = ffk.relnamespace

I'm hoping 'snakess' or you angleo can resolve this, we're going to have to delay the release of 1.1.1 until we come to a conclusion on this.

[angelo]

I'll have a look a.s.a.p., but I'm not sure I'll get to it tonight. Tomorrow night will definitely be pgsql night again....

[snakess]

Mike, the query looks correct to me except for the namespace join you mentioned. The question is if the result is what you want. \"pfk.relname\" will be the table the constraint is on and \"ffk.relname\" the table that are referenced.

Refering to the original two tables:

Code: Select all

create table client
(
  client_id int primary key,
  name varchar(80)
);
create table orders
(
  order_id int primary key,
  client_id int references client,
  description varchar(80)
);

Looking at this in MSSQL Enterprise Manager the orders table is listed as \"Foreign key table\" and client as \"Primary key table\". That makes sense to me. After all it is the orders table that has the foreign key constraint.

Summary:

Code: Select all

                           client                      |  orders         
---------------------------------------------------------------------------
MSSQL                      Primary key table              Foreign key table
PostgreSQL                 Referenced table               Referencing table
MyGen  MSSQL               PrimaryTable                   ForeignTable
MyGen  PGSQL               ForeignTable                   PrimaryTable

The naming in MyGen PGSQL seems inconsistent with the others.
I don't know what problems changing that would cause.

[mike.griffin]

Okay Gentleman, there's been a great reversal in the time-space continuim. Give this beta of 1.1.1 a try, it's nearly complete now. We planning on releasing Friday night. You guys have been a terrific help, same with the folks helping out on SQLite and MySQL, it's great to have users really willing to help out (and hold our feet to the fire). Thanx

http://download.mygenerationsoftware.com/mygenv111.exe

[snakess]

Yep, now it looks the same for both MSSQL and PostgreSQL.

[mike.griffin]

Want to see something cool, change one of the dOOdad template GUI AddListBox statements to AddCheckBoxList, this is very cool, Justin added it. It can also be real handy when he have a bunch of options to set like the Gentle.NET and Opf3 templates, it will save you lots of space and make your template cleaner.

[angelo]

Hi there - this looks great! Thanks Mike, I'm going to call Poland again!

Angelo

[snakess]

Want to see something cool, change one of the dOOdad template GUI AddListBox statements to AddCheckBoxList

Nice!!! That's a great feature.