CODEWITHSUNDEEP
sqlite
BorisV
BorisV

Reputation: 699

SQLite: making foreign key unique

I am using SQLite 3.8.11 for Mac OS X app . My goal is to create a table and then inside reference some field in other table. For example:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

However, I want to insert trackartist into track so that each row will contain unique trackartist value. In case the trackartist value is already inside the track - the new value must be ignored. How can I do that in this setting? UPDATE OR IGNORE command seems not working, values are duplicated.

Upvotes: 3

Views: 2643

Answers (2)

Parsa
Parsa

Reputation: 3236

Perhaps this would work?

INSERT OR REPLACE INTO track (trackartist) VALUES '123'
WHERE NOT EXISTS(SELECT 1 FROM track WHERE trackartist="123");

See here for detailed documentation.

EDIT: Correct syntax is following:

INSERT OR REPLACE INTO track (trackartist) SELECT '123' WHERE NOT EXISTS(SELECT 1 FROM track WHERE trackartist="123");

Upvotes: 0

Rob
Rob

Reputation: 438467

These are two separate issues: The FOREIGN KEY specifies merely that the value being inserted must exist in the other table, but doesn't specify how many times it may appear in this table. The UNIQUE constraint, on the other hand, requires that the value may not appear more than once in this table.

I'm not sure if it makes sense with the "artist-track" model, but let's imagine that you really wanted to specify that an artist can't have more than one track. You would therefore specify not only that trackartist references artist(artistid), but also that it is UNIQUE:

CREATE TABLE track(
  trackname TEXT,
  trackartist INTEGER UNIQUE,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

Upvotes: 6

Related Questions