CODEWITHSUNDEEP
mysqlsqlforeign-keys
user3623571
user3623571

Reputation: 47

Inserting data with foreign keys and avoiding duplication

I'm very beginner at MySQL and have just started to play around with foreign keys and INNER JOIN operator. So I've made a few tables like:

CREATE TABLE `models` 
(
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, 
`name` VARCHAR (255) NOT NULL, 
`price` MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY( `id` )
);

CREATE TABLE `vendors` 
(
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,  
`name` VARCHAR (255) NOT NULL,
`id_model` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY( `id` ),
FOREIGN KEY (`id_model`) REFERENCES models(`id`)
);

CREATE TABLE `cars` 
(
`serial_number` MEDIUMINT UNSIGNED NOT NULL,  
`id_vendor` TINYINT UNSIGNED NOT NULL,
FOREIGN KEY (`id_vendor`) REFERENCES vendors(`id`),
PRIMARY KEY( `serial_number` )
);

I know how to get output with that. However, the problem is that I don't know how to insert data properly. All I can do is insert data table by table. But how to do it in one query, and if I am inserting Honda Civic and already have Honda Accord, for example, wouldn't it duplicate Honda vendor in the database?

Upvotes: 0

Views: 95

Answers (2)

Cătălin Stan
Cătălin Stan

Reputation: 527

It seems that the structure of the database is not really coherent. Maybe I don't understand what you are trying to do, but ... anyway, here goes.

Assuming that what you want to do is store a list a of cars in a properly normalized relational database, first thing you want to do is think what is happenning in "real life":

  • Manufacturers (vendors) make/sell cars
  • Manufacturers (vendors) make different models of cars
  • Cars have a model (and a serial number in your case)
  • Models belong to a vendor (manufacturers)

Considering this, your table structure is:

Vendors
- id
- name

Models
 - id
 - name
 - vendor ( foreign key => vendor.id )

Cars    
- id
- serial_number
- model ( foreign key => model.id )

You don't need to have a reference to the vendor in the cars table becoause you have a reference to the model, which in turn has a reference to the vendor.

Whe inserting, you do it one by one, making sure that the foreign key entries already exist.

  • When you insert a car object, you just need to provide the model id.
  • When you insert a model object you need to provide a vendor id.

So the Honda Civic/Accord situation does not duplicate Honda. The Tables should be something like this:

Vendor

id, name
1, "Honda" 

Model

id, name, vendor
1, "Civic", 1
2, "Accord", 1

Cars

id, serial_no, model
1, "A serial", 2 -> a honda accord
2, "Another serial", 1 -> a honda civic

Hope this helps somewhat.

Upvotes: 1

qxg
qxg

Reputation: 7036

You do need to check if duplicated record exists yourself.

IF EXISTS (SELECT * FROM vendors WHERE Name = 'Honda')
BEGIN
    -- Insert into cars with existing vendor id
END
ELSE
BEGIN
    IF EXISTIS (SELECT * FROM models WHERE Name = 'your model name')
    BEGIN
        -- insert into vendors with existing model id
    END
    ELSE
    BEGIN
        -- insert into models
        -- insert into vendors
        -- insert into cars
    END
END

You can create stored procedure for it and pass car, vendor and model as parameters. Or you can list models first, insert them; then all vendors and all cars. Just a silly answer. Welcome more sophisticated solutions.

Upvotes: 0

Related Questions