Hello,
I am setting up a database of inventory for our computers. We have a table
called tblcomputers and a table called tblusers. Most of our computers are
matched to a login, e.g. the computer name GSMITH would be matched to a login
named GSMITH. There may also be a second computer called GSMITH2 which would
be matched to the GSMITH logon. So I have a one to many relationship of
computers to logins.
We also have a field called FLOOR and one called ROOM. On tblComputers, we
only want to enter information into these fields if the computer name is not
matched to a user id. For instance we have some scannner machines and guest
machines. How would I say that the FLOOR and ROOM fields are required only
if the computer ID isn't matched to a user?
I assume you have something similar to this (all SQL tested using
ANSI-92 Query mode):
CREATE TABLE ComputerRooms (
floor_number INTEGER NOT NULL,
CHECK (floor_number BETWEEN 1 AND 6),
room_number INTEGER NOT NULL,
CHECK (room_number BETWEEN 1 AND 20),
UNIQUE (floor_number, room_number)
)
;
CREATE TABLE tblusers (
userID VARCHAR(8) NOT NULL UNIQUE
)
;
CREATE TABLE tblcomputers (
computer_name VARCHAR(8) NOT NULL UNIQUE
)
;
CREATE TABLE tbllogins (
userID VARCHAR(8)
REFERENCES tblusers (userID),
computer_name VARCHAR(8) NOT NULL UNIQUE
REFERENCES tblcomputers (computer_name),
UNIQUE (userID, computer_name)
)
;
You *could* use a table-level CHECK constraint to test for the
existence of rows in the logins table...
CREATE TABLE tblnonusercomputerlocations (
computer_name VARCHAR(8) NOT NULL UNIQUE
REFERENCES tblcomputers (computer_name),
CONSTRAINT cannot_be_a_user_computer
CHECK (NOT EXISTS (
SELECT * FROM
tbllogins
WHERE tblnonusercomputerlocations.computer_name =
tbllogins.computer_name)
),
floor_number INTEGER NOT NULL,
room_number INTEGER NOT NULL,
FOREIGN KEY (floor_number, room_number)
REFERENCES ComputerRooms (floor_number, room_number)
);
....however, I'm coming around to the idea that the CHECK constraints
should avoid referencing rows in other tables. This is borne out by
both the SQL-92 standard, for which CREATE ASSERTION syntax is
provided, and the Jet implementation, which checks constraints on a
table-by-table basis rather than on a single SQL statement basis (see
http://groups.google.com/group/microsoft.public.access/msg/
e4cd2c5badd26bc8).
An alternative design, one that doesn't involve CHECK constraints, is
a subclassing approach e.g.
CREATE TABLE Computers (
computer_name VARCHAR(8) NOT NULL UNIQUE,
location_type CHAR(4) DEFAULT 'Room' NOT NULL,
CHECK (location_type IN ('Room', 'User')),
UNIQUE (computer_name, location_type)
)
;
CREATE TABLE Logins (
userID VARCHAR(8)
REFERENCES tblusers (userID),
computer_name VARCHAR(8) NOT NULL UNIQUE,
UNIQUE (userID, computer_name),
location_type CHAR(4) NOT NULL,
CHECK (location_type = 'User'),
FOREIGN KEY (computer_name, location_type)
REFERENCES Computers (computer_name, location_type)
)
;
CREATE TABLE NonUserComputerLocations (
computer_name VARCHAR(8) NOT NULL UNIQUE,
location_type CHAR(4) NOT NULL,
CHECK (location_type = 'Room'),
FOREIGN KEY (computer_name, location_type)
REFERENCES Computers (computer_name, location_type),
floor_number INTEGER NOT NULL,
room_number INTEGER NOT NULL,
FOREIGN KEY (floor_number, room_number)
REFERENCES ComputerRooms (floor_number, room_number)
)
;
With the above design, a computer can appear once in Logins or once in
NonUserComputerLocations (name could be improved!) but not to both
simultaneously. OK, so I did use CHECK constraints but the ones in the
alternative design can be replaced by column-level (record-level)
Validation Rules and even as CHECK constraints will not suffer the
possible timing issues that the multi-table CHECK could.
Jamie.
--
