Such constraints can indeed be implemented in ACE/JET using CHECK constraints.
The person who said they would usually use triggers for this kind of thing possible isn't aware of the difference between CHECK constraints in ACE/Jet and SQL Server respectively: in SQL Server they cannot include subqueries, meaning they cannot refer to values in other rows in the same table or to other tables, whereas in ACE/Jet there can.
In an ideal (but as yet AFAIK non-existent) SQL-92 product, the uniqueness described would be implemented using an ASSETION, being at the schema level. Because CHECK constraints are table-level and are only checked when the table on which they are defined are UPDATEd or INSERTed, you would need to put appropriate CHECK constraints on all the referencing table (the same would apply to SQL Server triggers). Here's a quick example:
CREATE TABLE Parent
(
parent_ID INTEGER NOT NULL IDENTITY UNIQUE,
data_col INTEGER NOT NULL
)
;
CREATE TABLE Child1
(
parent_ID INTEGER NOT NULL
REFERENCES parent (parent_ID),
data_col INTEGER NOT NULL
)
;
CREATE TABLE Child2
(
parent_ID INTEGER NOT NULL
REFERENCES parent (parent_ID),
data_col INTEGER NOT NULL
)
;
ALTER TABLE Child1 ADD
CONSTRAINT child1__no_dups_in_child2
CHECK (NOT EXISTS (
SELECT *
FROM Child1 AS C1
INNER JOIN Child2 AS C2
ON C1.parent_ID = C2.parent_ID
))
;
ALTER TABLE Child2 ADD
CONSTRAINT child2__no_dups_in_child1
CHECK (NOT EXISTS (
SELECT *
FROM Child1 AS C1
INNER JOIN Child2 AS C2
ON C1.parent_ID = C2.parent_ID
))
;
However, I'm wondering if you have subclasses (i.e. each entity represented by an ID can be typed), in which case you should be able to use FOREIGN KEYs and row-level CHECK constraints (or Validation Rules if you are more comfortable with the MS Access interface than SQL DLL that is required for CHECK constraints). The logic will be easier to implement than table-level CHECK constraints, just watch for cycles in CASCADE referential actions. Here's another simple example:
CREATE TABLE Parent
(
parent_ID INTEGER NOT NULL IDENTITY,
child_type VARCHAR(4) NOT NULL,
CONSTRAINT child_type__values
CHECK (child_type IN ('Boy', 'Girl')),
UNIQUE (child_type, parent_ID)
)
;
CREATE TABLE Girls
(
parent_ID INTEGER NOT NULL,
child_type VARCHAR(4) DEFAULT 'girl' NOT NULL,
CONSTRAINT girl_child_type__must_be_girl
CHECK (child_type = 'girl'),
FOREIGN KEY (child_type, parent_ID)
REFERENCES Parent (child_type, parent_ID),
data_col INTEGER NOT NULL
)
;
CREATE TABLE Boys
(
parent_ID INTEGER NOT NULL,
child_type VARCHAR(4) DEFAULT 'boy' NOT NULL,
CONSTRAINT boy_child_type__must_be_boy
CHECK (child_type = 'boy'),
FOREIGN KEY (child_type, parent_ID)
REFERENCES Parent (child_type, parent_ID),
data_col INTEGER NOT NULL
)
;