On 13/01/2009, Damian Brasher <lug@???> wrote:
>
> Damian Brasher wrote:
>
> >
> > DROP TABLE IF EXISTS attendee;
> > CREATE TABLE attendee (
> > id INT NOT NULL AUTO_INCREMENT,
>
>
> But this is better...
>
>
> CREATE DATABASE bab_register;
>
> USE bab_register;
>
> DROP TABLE IF EXISTS attendee;
> CREATE TABLE attendee (
> id INT NOT NULL AUTO_INCREMENT,
> forename VARCHAR(50) NOT NULL,
> surname VARCHAR(50) NOT NULL,
> email VARCHAR(50),
> member ENUM('yes','no') NOT NULL,
> new_attendee ENUM('yes','no') NOT NULL,
> mac VARCHAR(50),
> PRIMARY KEY (id),
> FOREIGN KEY (mac) REFERENCES macs (mac)
> ) COMMENT "HLUG BaB meeting register Southampton Uni (ECS)";
>
> DROP TABLE IF EXISTS macs;
> CREATE TABLE macs (
>
> id INT NOT NULL,
> mac VARCHAR(50) NOT NULL,
> PRIMARY KEY (mac),
>
> FOREIGN KEY (id) REFERENCES attendee (id)
> ) COMMENT "MAC Address entries";
>
>
No, that is not going to work.
You are back to the situation of one attendee having one mac address, also
you cannot delete a row from macs as it is referred to by attendees, and
likewise you cannot delete a row from attendees because it is referred to by
macs, nor can you add any data.
Try this-
CREATE DATABASE bab_register;
USE bab_register;
DROP TABLE IF EXISTS attendee;
CREATE TABLE attendee (
id INT NOT NULL AUTO_INCREMENT,
forename VARCHAR(50) NOT NULL,
surname VARCHAR(50) NOT NULL,
email VARCHAR(50),
member ENUM('yes','no') NOT NULL,
new_attendee ENUM('yes','no') NOT NULL,
PRIMARY KEY (id),
) COMMENT "HLUG BaB meeting register Southampton Uni (ECS)";
DROP TABLE IF EXISTS macs;
CREATE TABLE macs (
id INT NOT NULL AUTO_INCREMENT,
attendee_id INT NOT NULL,
mac VARCHAR(50) NOT NULL,
PRIMARY KEY (mac),
FOREIGN KEY (attendee_id) REFERENCES attendee (id)
) COMMENT "MAC Address entries";
This says that an attendee must exist for each mac address, but says nothing
about the number of mac addresses per attendee. To add a mac address, you
must have an attendee, and you cannot delete an attendee if there are any
mac addresses referring to it.
Jim
