David Neto David Neto - 2 months ago 10
MySQL Question

SQL: How to update from 'tableA pointing to tableB' to 'tableA poiting to tableC pointing to tableB' without losing existing element relation?

Basically I want an intermediary

Book
, between what was a direct link from
Line
to
User
.
Line
elements already exist and can't lose the relation to corresponding already existing
User
element, so a default
Book
element needs to be created which handles already existing data.

Tables were created like this:

User (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

Line (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(300),
id_user INT UNSIGNED NOT NULL,
FOREIGN KEY (id_user) REFERENCES User(id)
);


Now I need to create some SQL code that would produce tables like this:

User (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

Book (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(300),
id_user INT UNSIGNED NOT NULL,
FOREIGN KEY (id_user) REFERENCES User(id)
);

Line (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(300),
id_book INT UNSIGNED NOT NULL,
FOREIGN KEY (id_book) REFERENCES Book(id)
);


... except that data already exists. so I have Line elements pointing to User elements and I can't create new tables from scratch. So I need the SQL code to modify the existing structure.

Basically I just want users to have books and books to have lines, instead of users have lines. And already existing lines to get a connection with the owner users. What would be the best approach?

///////////////////////////////////////////////////////

Some sample data representing what my database looks like currently:

Table user(id,username)
- 1, user1
- 2, user2
- 3, user3

Table line(id,title,id_user)
- 1, line1, 1
- 2, line2, 1
- 3, linex, 1
- 4, line1, 2
- 5, liney, 2


The output should be the following database:

Table user(id,username)
- 1, user1
- 2, user2
- 3, user3

Table book(id,title,id_user)
- 1, 'default', 1
- 2, 'default', 2
- 3, 'default', 3

Table line(id,title,id_book)
- 1, line1, 1
- 2, line2, 1
- 3, linex, 1
- 4, line1, 2
- 5, liney, 2


////////////////////////////////

What my pipeline currently looks like, my main problem is producing the final query:

create table book
$sql = "CREATE TABLE IF NOT EXISTS book(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(300),
id_user INT UNSIGNED NOT NULL,

FOREIGN KEY (id_user) REFERENCES User(id)
); ";

$conn->exec($sql);


//insert default book corresponding to every user
$sql = "INSERT INTO Book(title, id_user) SELECT DISTINCT 'default', id FROM User;";
$conn->exec($sql);

//insert id_book foreign key into Line table pointing to Book table
$sql = "ALTER TABLE Line ADD id_book INT;"

$conn->exec($sql);

//make Line.id_book foreign key pointing to Line.id
$sql="ALTER TABLE Line ADD FOREIGN KEY (id_book) REFERENCES User(id);"

$conn->exec($sql);

//unfinished
$sql = "UPDATE
Line
SET
Line.id_book = Book.id
WHERE
Line.id_user = Book.id_user"

Answer

Create table book (like you already did):

CREATE TABLE IF NOT EXISTS book(
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(300),
      id_user INT UNSIGNED NOT NULL,     
      FOREIGN KEY (id_user) REFERENCES User(id)
); 

Populate the new table:

INSERT INTO Book(title, id_user) SELECT 'default', id FROM User;

I removed DISTINCT from your code because id is primary/unique and thus every selected row is guaranteed to be unique.

Add column id_book to the line table:

ALTER TABLE Line ADD id_book INT UNSIGNED;

It must be INT UNSIGNED to match the referenced data type.

Add foreign key:

ALTER TABLE Line ADD FOREIGN KEY (id_book) REFERENCES book(id); 

It should reference a book, not a user like in your code.

Update data (Relate the line with the users (first) book):

UPDATE Line
SET Line.id_book = (
  SELECT book.id
  FROM book
  WHERE book.id_user = Line.id_user
  ORDER BY book.id
  LIMIT 1
);

Drop line.id_user column:

-- use "SHOW CREATE TABLE line" to find out the foreign key name
ALTER TABLE Line DROP FOREIGN KEY line_ibfk_1;
ALTER TABLE Line DROP id_user;

http://sqlfiddle.com/#!9/3faa13/1

Note: If a user has two or more books, you don't know wich one to link with a line. In your question you didn't define how to handle this case. So i decided to take the first book by id (ORDER BY book.id LIMIT 1).

The UPDATE statement could also be:

UPDATE Line
SET Line.id_book = (
  SELECT MIN(book.id)
  FROM book
  WHERE book.id_user = Line.id_user
);
Comments