I need some help with an error I'm getting with SQL. My goal is to make a simple proof of concept music database.
Te problem is that when using foreign keys I'm getting these errors:
Error: near line 134: near "key": syntax error
I'm not sure what I'm not seeing with this syntax problem but any help is appreciated :)
My code:
CREATE TABLE IF NOT EXISTS [Account]
(
[AccountId] varchar(45) NOT NULL,
[Name] varchar(45) NOT NULL,
[Membership] varchar(45) NOT NULL,
[ArtistId] int NULL,
[CreationDate] date NOT NULL,
PRIMARY KEY ([AccountId])
);
CREATE TABLE IF NOT EXISTS [Favorites]
(
[Favorites] int NOT NULL,
[AccountId] varchar(45) NOT NULL,
[SongId] varchar(45) NOT NULL,
PRIMARY KEY ([Favorites]),
KEY[FK_117] ([SongId]),
CONSTRAINT [FK_115]
FOREIGN KEY [FK_117] ([SongId])
REFERENCES [Song] ([SongId]),
KEY [FK_120] ([AccountId]),
CONSTRAINT [FK_118]
FOREIGN KEY [FK_120] ([AccountId])
REFERENCES [Account] ([AccountId])
);
CREATE TABLE IF NOT EXISTS [History]
(
[AccountId] varchar(45) NOT NULL,
[SongId] varchar(45) NOT NULL,
PRIMARY KEY ([AccountId]),
KEY [FK_124] ([AccountId]),
CONSTRAINT [FK_122]
FOREIGN KEY [FK_124] ([AccountId])
REFERENCES [Account] ([AccountId]),
KEY [FK_127] ([SongId]),
CONSTRAINT [FK_125]
FOREIGN KEY [FK_127] ([SongId])
REFERENCES [Song] ([SongId])
);
CREATE TABLE IF NOT EXISTS [Song]
(
[SongId] varchar(45) NOT NULL,
[ArtistId] varchar(45) NOT NULL,
[AlbumId] varchar(45) NOT NULL,
[Duration] float NOT NULL,
[Listens] int NULL,
[CreationDate] date NOT NULL,
PRIMARY KEY ([SongId]),
KEY [FK_66] ([AlbumId]),
CONSTRAINT [FK_64]
FOREIGN KEY [FK_66] ([AlbumId])
REFERENCES [Album] ([AlbumId]),
KEY [FK_70] ([ArtistId]),
CONSTRAINT [FK_68]
FOREIGN KEY [FK_70] ([ArtistId])
REFERENCES [Artist] ([ArtistId])
);
create table IF NOT EXISTS [Artist]
(
[ArtistId] varchar(45) not null ,
[Duration] float not null ,
[AccountId] varchar(45) not null ,
[Listens] int null ,
primary key ([ArtistId]),
key [FK_73] ([AccountId]),
constraint [FK_71]
foreign key [FK_73] ([AccountId])
references [Account] ([AccountId])
);
create table IF NOT EXISTS [Album]
(
[AlbumId] varchar(45) not null ,
[ArtistId] varchar(45) not null ,
[Duration] float not null ,
[Listens] int null ,
[CreationDate] date not null ,
primary key ([AlbumId]),
key [FK_76] ([ArtistId]),
constraint [FK_74]
foreign key [FK_76] ([ArtistId])
references [Artist] ([ArtistId])
);
create table IF NOT EXISTS [AlbumSongs]
(
[AlbumId] varchar(45) not null ,
[SongId] varchar(45) not null ,
primary key ([AlbumId]),
key [FK_131] ([AlbumId]),
constraint [FK_129]
foreign key [FK_131] ([AlbumId])
references [Album] ([AlbumId]),
key [FK_134] ([SongId]),
constraint [FK_132]
foreign key [FK_134] ([SongId])
references [Song] ([SongId])
);
create table IF NOT EXISTS [Playlists]
(
[PlaylistName] varchar(45) not null ,
[AccountId] varchar(45) not null ,
[CreationDate] date not null ,
primary key ([PlaylistName]),
key [FK_88] ([AccountId]),
constraint [FK_86]
foreign key [FK_88] ([AccountId])
references [Account] ([AccountId])
);
create table IF NOT EXISTS [PlaylistSongs]
(
[PlaylistId] integer not null ,
[SongId] varchar(45) not null ,
primary key ([PlaylistId]),
key [FK_110] ([SongId]),
constraint [FK_108]
foreign key [FK_110] ([SongId])
references [Song] ([SongId])
);
--Insert Info
INSERT INTO Song(SongId, ArtistId, AlbumId, Duration, Listens, CreationDate)
VALUES ('Feel it Still', 'Confetti', 'Rampage', 127, 120000, 10-20-2000),
('Hot', '2WEI', 'Guilty', 189, 9753, 7-9-2000),
('Run', 'Queen', 'The Cinema', 89, 89600, 1-20-2000);
INSERT INTO Account (AccountId, Name, Membership, ArtistId, CreationDate)
VALUES (1, 'Tom B. Erichsen', 'Premium', 'Confetti', 10-20-2000),
(2, 'Anna L. Lander', 'Free', 'Queen', 10-20-1999),
(3, 'Ned B. Flanders', 'Premium', null, 10-20-2010);
INSERT INTO Favorites (AccountId, SongId)
VALUES (1, 'Feel it Still'),
(2, 'Hot'),
(3, 'Run');
INSERT INTO History(AccountId, SongId)
VALUES (1, 'Run'),
(3, 'Hot'),
(2, 'Feel it Still');
INSERT INTO Artist(ArtistId, AccountId, Duration, Listens)
VALUES ('Confetti','Tom B. Erichsens', 97091, 3457678),
('2WEI', null, 23897, 34556),
('Queen','Ned B. Flanders', 490783, 89734);
INSERT INTO Album(ArtistId, AlbumId)
VALUES ('Confetti', 'Rampage'),
('2WEI', 'Guilty'),
('Queen', 'The Cinema');
INSERT INTO AlbumSongs(AlbumId, SongId)
VALUES ('Rampage', 'Feel it Still'),
('Guilty', 'Hot'),
('The Cinema', 'Run');
INSERT INTO Playlists(PlaylistName, AccountId, CreationDate)
VALUES ('Fun Car Songs', 2, 10-20-2009),
('Running stuff', 2, 05-21-1999),
('Lol',1, 07-21-1999);
INSERT INTO PlaylistSongs(PlaylistId, SongId)
VALUES ('Fun Car Songs', 'Hot'),
('Fun Car Songs', 'Feel it Still'),
('Fun Car Songs', 'Run');
The Error's:
-- Loading resources from main.sql
Error: near line 14: near "key": syntax error
Error: near line 35: near "key": syntax error
Error: near line 55: near "key": syntax error
Error: near line 79: near "key": syntax error
Error: near line 96: near "key": syntax error
Error: near line 114: near "key": syntax error
Error: near line 134: near "key": syntax error
Error: near line 150: near "key": syntax error
Error: near line 165: no such table: Song
Error: near line 175: no such table: Favorites
Error: near line 180: no such table: History
Error: near line 185: no such table: Artist
Error: near line 190: no such table: Album
Error: near line 195: no such table: AlbumSongs
Error: near line 200: no such table: Playlists
Error: near line 205: no such table: PlaylistSongs
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
There are some lines starting with the word key, what do you expect them to do? Like this one:
key [FK_117] ([SongId]),
I think that these are your problem.