Hi,

I am creating a database of theatre productions and currently have a very basic setup with the below tables:-

production
prod_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
prod_name VARCHAR(255) NOT NULL

theatre
theatre_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
theatre_name VARCHAR(255) NOT NULL UNIQUE

prodtheatre
prodid INT NOT NULL,
theatreid INT NOT NULL,
PRIMARY KEY (prodid, theatreid)

I have created 'add / edit production' forms into which you can enter the 'prod_name' and 'theatre_name'.

Currently if you enter a 'theatre_name' that already exists in the column it does not allow the entry to be added (given the field is 'UNIQUE') and simply displays the error message. What I would like to happen is for any such entries to be permitted and assigned the same 'theatre_id' as any pre-existing entry. (Not using the 'UNIQUE' command simply creates a new entry with a separate 'theatre_id' but with an identical name; I need all theatres with the same name assigned to the same id).

And I don't want to have to first create theatres in a separate 'add / edit' form.

Thanks,

Andy