Обсуждение: two tables - foreign keys referring to each other...
Hey to all,
I got two tables, linked to each other. How can I tell the first CREATE TABLE
(institute_t) to wait to check the foreign key for the second table??? just
like "hold on a little bit... you'll receive your admin_t" :-) ? I thoght
DEFERRABLE, DEFERRED and transaction with BEGIN/COMMIT take care of this.
..or generally: how do you create two crosslinked foreign keyed tables?
hopefully an easy problem for the real professionals!
-----------------> here we go
BEGIN; -- begin table transaction -- Only Postgresql
CREATE TABLE institute_t (
name VARCHAR(48) PRIMARY KEY,
street VARCHAR(48) NOT NULL,
zip VARCHAR(16),
town VARCHAR(32) NOT NULL,
country CHAR(2) NOT NULL, /* country codes ISO-3166*/
phone VARCHAR(32) NOT NULL,
fax VARCHAR(32),
admin VARCHAR(16) REFERENCES admin_t
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE
INITIALLY DEFERRED
);
CREATE TABLE admin_t (
login VARCHAR(16) PRIMARY KEY,
password VARCHAR(16) NOT NULL,
email VARCHAR(32) NOT NULL,
real_name VARCHAR(32) NOT NULL,
street VARCHAR(48) NOT NULL,
zip VARCHAR(16),
town VARCHAR(32) NOT NULL,
country CHAR(2) NOT NULL, /* country codes -- refer to
ISO-3166*/
phone VARCHAR(32) NOT NULL,
fax VARCHAR(32),
access INTEGER NOT NULL,
institute VARCHAR(48) REFERENCES institute_t
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE
INITIALLY DEFERRED
);
COMMIT;
of course I get the ERROR, that admin_t doesn't exist. So? help the stupid!
pls!
best greets,
Chris
ThX Kirby, ThX Martijn, as you can see I'm new to the SQL- and database world. My policy for this project is to FORCE an admin to be in an institute. If one admin behaves bad for the database project the institute is responsible for her/him. This institute would be represented by a special admin (therefore the link back). Anyway, I see that crosslinking is really a little bit tricky... I will do this check in the application level (Java) and see, if this is easier and even necessary. For now I might use only admin(fk)->institute. This is necessary to trace back responsibility. as beginner I appreciate good hints, cheers, Chris > > IMHO, you should consider not having the admin table have a link to the > institute table. If you want to find the institute a particular admin > is connected with, find that look in the institutes table. The astute > reader will note the advice is symmetric, you can not have a link from > the institute to the admin. If you don't want to have dangling admin's > you might be able to get a trigger/stored procedure to do it for you > (Note:I done triggers in Oracle, never in PostGres so take that with a > grain of salt. I would be shocked if you couldn't do it with a Trigger > under PostGres. I believe it us commonly done with long objects as a > matter of fact). > > Do what you like, and I hope this helps. > > Kirby >
You have to use ALTER TABLE to add the constraint to one of the tables. Deferred refers to the checking of the constraint itself, not really to the check to see if the table is there. On Wed, 21 Feb 2001, Chris Czeyka wrote: > Hey to all, > > I got two tables, linked to each other. How can I tell the first CREATE TABLE > (institute_t) to wait to check the foreign key for the second table??? just > like "hold on a little bit... you'll receive your admin_t" :-) ? I thoght > DEFERRABLE, DEFERRED and transaction with BEGIN/COMMIT take care of this. > > ..or generally: how do you create two crosslinked foreign keyed tables? > > hopefully an easy problem for the real professionals! > > > -----------------> here we go > BEGIN; -- begin table transaction -- Only Postgresql > CREATE TABLE institute_t ( > name VARCHAR(48) PRIMARY KEY, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > town VARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > admin VARCHAR(16) REFERENCES admin_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > > CREATE TABLE admin_t ( > login VARCHAR(16) PRIMARY KEY, > password VARCHAR(16) NOT NULL, > email VARCHAR(32) NOT NULL, > real_name VARCHAR(32) NOT NULL, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > town VARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes -- refer to > ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > access INTEGER NOT NULL, > institute VARCHAR(48) REFERENCES institute_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > COMMIT; > > > of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! > pls! > > best greets, > Chris >
On Tue, Feb 20, 2001 at 11:34:30PM -0800, Stephan Szabo wrote: > > You have to use ALTER TABLE to add the constraint to one of the tables. Maybe I am stating the obvious but you should make sure that you include the ALTER TABLE statements in the *.sql files that you use to create the tables, rather than running them from the psql prompt. Otherwise you'll have trouble to figure out what exactly you did when you come back to the database later; as foreign keys don't show up as 'foreign keys' in schema dumps, but as a set of <unnamed> triggers. Those are quite hard to read, or interpret as what they essentially are, i.e. foreign keys (depending on your philosophical outlook, that is, whether you consider the essence of your foreign keys to be a set of triggers, or vice versa ;-)). Regards, Frank
I think, if it is relationship many-to-many (one admin can be in many institute, and one institute can has many admin, you should use relation table, see below. > -----------------> here we go > BEGIN; -- begin table transaction -- Only Postgresql > CREATE TABLE institute_t ( > name VARCHAR(48) PRIMARY KEY, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > town VARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > admin VARCHAR(16) REFERENCES admin_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > create table institute_admin (row int primary key,name varchar(48) references institute_t,login varchar(16) references admin_t ); > CREATE TABLE admin_t ( > login VARCHAR(16) PRIMARY KEY, > password VARCHAR(16) NOT NULL, > email VARCHAR(32) NOT NULL, > real_name VARCHAR(32) NOT NULL, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > town VARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes -- refer to > ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > access INTEGER NOT NULL, > institute VARCHAR(48) REFERENCES institute_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > COMMIT; > If you have diffarant relation, describe it. ----------------------------- Grigoriy G. Vovk
On Wed, Feb 21, 2001 at 12:30:51PM +0900, Chris Czeyka wrote: [big snip] > of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! > pls! I think the general idea is to create the tables first without the constraints and then do ALTER TABLE ADD CONSTRAINT afterwards. HTH, Martijn