(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.
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846914666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi3.asc | gpg --import"
// Fingerprint: 44A3 C1EC B71E C71A B4C2 9AA6 C818 847C 55CB A4EE
// Keyserver: www.keyserver.net Key-ID: 0x55CBA4EE
pgpMDYohLqvta.pgp
Description: PGP signature
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
