Michael Monnerie wrote: > (this mail is easier to read with a fixed charset) > > Hi, I have a question to the SQL gurus on this list. I've extended the > domains table to resolve the n:m relation customer <-> domaingroups: > > CREATE TABLE zmi_domains ( > domain_idnr BIGSERIAL UNIQUE, --- domain id > domain VARCHAR(200) UNIQUE NOT NULL, --- name of this domain > domaingrp_idnr BIGSERIAL, --- all domains which alias > to each other are in the same group > --- if not specified, > automatically assigns next number > --- will be automatic for > most customers (only having 1 domain) > maxaccounts int8, --- maximum number of > accounts allowed > userquota int8, --- default quota per user > in this domain > domainquotasoft int8, --- quota of domain total > (soft limit, issues warnings) > domainquotahard int8, --- quota of domain total > (hard limit, bounce e-mail) > comment text, > CONSTRAINT zmidom_pk PRIMARY KEY(domain) > ); > > --- n:m table for customers and their domaingroups > --- a customer can have several domains which belong to an alias domain group > --- a customer can have several domain groups, if those domains do not alias > to each other > --- a customer can of course have only one domain, being alone in it's group > then > CREATE TABLE zmi_custdomains ( > client_idnr INT8 NOT NULL --- customers id > REFERENCES zmi_customers(id), > domaingrp_idnr INT8 NOT NULL, --- domain group id > pridomain_idnr INT8 NOT NULL --- this is the id of the > primary domain of this group > REFERENCES zmi_domains(domain_idnr), > CONSTRAINT zmicustdom_pk PRIMARY KEY(client_idnr,domaingrp_idnr) > ); > > HOW can I make sure domaingrp_idnr can have only values that exist in > zmi_domains(domaingrp_idnr)? I can't find how I write a constraint > for that.
Since domaingrp_idnr is not unique, you can't use a plain constraint. You'll have to use a trigger. I've never done triggers in PGSQL but in the sqlite/create_tables.sql code you can see how it can be used to emulate foreign key constraints. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
