I have a database with 3 base tables:

LicenceNumbers
Customers and
Machines

There are MANY LicenceNumbers.
There are MANY Customers.
Each Customer can have MANY Machines.
Each Customer can have MANY LicenceNumbers.
Each Machine can have MANY LicenceNumbers.
ONE LicenceNumber can only be linked to ONE Customer OR ONE Machine.

In the real world that the database is trying to model, if a customer and not a machine is given a licence number then all the machines that are linked to that customer inherit the licence number.

How can I enforce this referential integrity in my database?

Regards
Owain Williams

__________________
Regards
Owain Williams

The table structure I would create, you will need one more table:
(... = what ever other fields you may need)
(PK = primary Key, FK = foreign key)

licenceNumbersTable
ID (PK, int, autoIncrement)
...
...

CustomersTable
ID (PK, int, autoIncrement)
...
...

MachinesTable
ID (PK, int, autoIncrement)
...
...

CustomerMachinesTable
ID (PK, int, autoIncrement)
MID (FK, int,(machineID))
CID (FK, int,(customerID))
LNID (FK, int,(licenceNumberID))
...
...

This would create a structure for you to put relationships in place. Enterprize manager has a useful graphical tool for this. Create this structure, click on diagrams, add all your tables to the diagram, click hold n drag the primary keys to the relavent forign keys.

Wind is your friend
Matt

Thank you Matt, that looks like the sort of thing I am after, I didn't think of adding the licence number primary key to the customer - machine linking table.

Regards
Owain Williams