1. Vertabelo
  2. Помилки
0
Started

sqlite generate er diagram from sql

Martin Chiu 9 років тому • оновлений 9 років тому • 3
Hello Vertabelo,
I am using your student account so you may not find my bug report a high priority. I will tell you though I tried to generate tables and a lot of things were not marked properly or tables were not linked properly. Mainly, the many to many tables. I have posted my SQL below so you may recreate the problem. It is fairly easy to reproduce. Anyway, the specific bugs you'll see are things like the table "genre" not being linked to anything and it also created a NULL primary key and marked other things as NULL that it shouldn't. The data base used is SQLite 3.7.x Well I hope this helps!


SQL USED

CREATE TABLE IF NOT EXISTS movie(id INTEGER PRIMARY KEY, title VARCHAR(150) NOT NULL, language CHAR(20), tagline VARCHAR(500), release_date DATE);

CREATE TABLE IF NOT EXISTS country(id INTEGER PRIMARY KEY, name CHAR(75) NOT NULL);

CREATE TABLE IF NOT EXISTS genre(id INTEGER PRIMARY KEY, type CHAR(50) NOT NULL);

CREATE TABLE IF NOT EXISTS theater(id INTEGER PRIMARY KEY, in_business INTEGER, website_url VARCHAR(100), mantinee_endtime DATETIME, phone_number CHAR(20) );

CREATE TABLE IF NOT EXISTS showtime(id INTEGER PRIMARY KEY, expires DATE NOT NULL, showtime DATETIME NOT NULL, movie_id INTEGER, showingtype_id INTEGER, theater_id INTEGER, FOREIGN KEY(movie_id) REFERENCES movie(id), FOREIGN KEY(theater_id) REFERENCES theater(id), FOREIGN KEY(showingtype_id) REFERENCES showingtype(id) );

CREATE TABLE IF NOT EXISTS showingtype(id INTEGER PRIMARY KEY, type CHAR(40) NOT NULL);

CREATE TABLE IF NOT EXISTS price(id INTEGER PRIMARY KEY, enddate DATE NOT NULL,cost DOUBLE NOT NULL, mantinee INTEGER NOT NULL, showingtype_id INTEGER, theater_id INTEGER, FOREIGN KEY(theater_id) REFERENCES theater(id), FOREIGN KEY(showingtype_id) REFERENCES showingtype(id));

CREATE TABLE IF NOT EXISTS state(id INTEGER PRIMARY KEY, state CHAR(2));

CREATE TABLE IF NOT EXISTS zipcode(id INTEGER PRIMARY KEY, zipcode CHAR(5));

CREATE TABLE IF NOT EXISTS location(id INTEGER PRIMARY KEY, street VARCHAR(100), state_id INTEGER, zipcode_id INTEGER, country_id INTEGER, FOREIGN KEY(state_id) REFERENCES state(id), FOREIGN KEY(zipcode_id) REFERENCES zipcode(id), FOREIGN KEY(country_id) REFERENCES country(id));

CREATE TABLE IF NOT EXISTS reviewer(id INTEGER PRIMARY KEY, first_name CHAR(50), last_name CHAR(50));

CREATE TABLE IF NOT EXISTS review(review VARCHAR(255) NOT NULL, title VARCHAR(150) NOT NULL, rating INTEGER, last_updated TIMESTAMP, date_posted DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, movie_id INTEGER, reviewer_id INTEGER, PRIMARY KEY(movie_id, reviewer_id), FOREIGN KEY(reviewer_id) REFERENCES reviewer(id), FOREIGN KEY(movie_id) REFERENCES movie(id));

CREATE TABLE IF NOT EXISTS cast_crew(id INTEGER PRIMARY KEY, first_name CHAR(50), last_name CHAR(50), date_death DATETIME, date_birth DATETIME, job_title VARCHAR(100));




CREATE TABLE IF NOT EXISTS movie_country(movie_id INTEGER, country_id INTEGER, PRIMARY KEY(movie_id, country_id) FOREIGN KEY(movie_id) REFERENCES movie(id),
FOREIGN KEY(country_id) REFERENCES country(id));

CREATE TABLE IF NOT EXISTS movie_genre(movie_id INTEGER, genre_id INTEGER, PRIMARY KEY(movie_id, genre_id) FOREIGN KEY(movie_id) REFERENCES movie(id), FOREIGN KEY(genre_id) REFERENCES genre(id));

CREATE TABLE IF NOT EXISTS movie_theater(movie_id INTEGER, theater_id INTEGER, PRIMARY KEY(movie_id, theater_id) FOREIGN KEY(movie_id) REFERENCES movie(id), FOREIGN KEY(theater_id) REFERENCES theater(id));

CREATE TABLE IF NOT EXISTS movie_cast_crew(movie_id INTEGER, cast_crew_id INTEGER, PRIMARY KEY(movie_id, cast_crew_id), FOREIGN KEY(movie_id) REFERENCES movie(id), FOREIGN KEY(cast_crew_id) REFERENCES cast_crew(id));




Under review
Agnieszka Kozubek-Krycuń (Chief Scientist) 9 років тому
Started
Agnieszka Kozubek-Krycuń (Chief Scientist) 9 років тому
The INTEGER PRIMARY KEYS should be marked as NOT NULL. We'll try to fix this error as soon as possible.

On the "genre" table not being linked to other tables: there is a syntax error in your SQL script. According to Sqlite syntax (https://www.sqlite.org/lang_createtable.html) the constraint definitions (in particular PRIMARY KEY and FOREIGN KEY definition) should be separated with a comma. If you add the missing comma, the reference will be parsed and added by Vertabelo.
Martin Chiu 9 років тому
Although the FOREIGN KEY AND PRIMARY KEY should be seperate with a comma. SQLite still generates my tables correctly.

Служба підтримки клієнтів працює на UserEcho