SQLTeam.com | Weblogs | Forums

Hi. need help with constraints.. stuck :(


#1

sqlite> .open test3.db
sqlite> PRAGMA foreign_keys=ON;
sqlite>
sqlite> CREATE TABLE ARTIST (Artistid INTEGER PRIMARY KEY, Artist_Name TEXT NOT NULL);
sqlite>
sqlite> CREATE TABLE TRACK (Trackid INTEGER PRIMARY KEY, Artistid INTEGER, Track_Name TEXT, Artist_Name TEXT NOT NULL, Release_Date TEXT,
...> CONSTRAINT Trackid_Artistid_PK PRIMARY KEY (Trackid, Artistid),
...> CONSTRAINT Trackid_FK FOREIGN KEY (Trackid) REFERENCES TRACK (Trackid),
...> CONSTRAINT Artistid_FK FOREIGN KEY (Artistid) REFERENCES ARTIST (Artistid));
Error: table "TRACK" has more than one primary key
sqlite>
sqlite> CREATE TABLE RADIO_SHOWS (RadioShowID INTEGER PRIMARY KEY, Artistid INTEGER, Artist_Name TEXT, Radio_Show_Name TEXT, DATE TEXT,
...> CONSTRAINT Radioshowid_Artistid_PK PRIMARY KEY (RadioShowID, Artistid),
...> CONSTRAINT RadioShowID_FK FOREIGN KEY (RadioShowID) REFERENCES RADIO_SHOWS (RadioShowID),
...> CONSTRAINT Artistid_FK FOREIGN KEY (Artistid) REFERENCES ARTIST (Artistid));
Error: table "RADIO_SHOWS" has more than one primary key
sqlite>
sqlite> CREATE TABLE GENRES (Styleid INTEGER PRIMARY KEY, Style_Name TEXT);
sqlite>
sqlite> CREATE TABLE USER (Userid INTEGER PRIMARY KEY, User_Name TEXt, country TEXT);
sqlite>
sqlite> CREATE TABLE PERSONAL_INFO (Name TEXT, Screen_Name TEXT, age INTEGER, gender TEXT);
sqlite>
sqlite> CREATE TABLE FAVS (Favoriteid INTEGER PRIMARY KEY, Favorite_Name TEXT, Favorite_Genre TEXT, YAY TEXT, NAY TEXT);
sqlite>
sqlite> CREATE TABLE LISTEN_server (Styleid INTEGER, Style_Name TEXT, Artistid INTEGER, Artist_Name TEXT, Trackid INTEGER, Track_Name TEXT,
...> CONSTRAINT Styleid_Artistid_Trackid_PK PRIMARY KEY (Styleid, artistid, trackid),
...> CONSTRAINT Styleid_FK FOREIGN KEY (Styleid) REFERENCES GENRES (Styleid,
...> CONSTRAINT Artistid_FK FOREIGN KEY (artistid) REFERENCES ARTIST (Artistid),
...> CONSTRAINT Trackid_FK FOREIGN KEY (trackid) REFERENCES TRACK (Trackid));
Error: near "CONSTRAINT": syntax error
sqlite>
sqlite> CREATE TABLE SETTINGS (settingsid INTEGER PRIMARY KEY, settings_name TEXT, USAGE INTEGER);


#2

bump


#3

Hello,
Please notice that this is a Microsoft SQL Server site so you may not find many people who know SQLite.