为什么在我没有设置唯一约束的数据上不断出现错误“Unique constraint failed”？

Question

我正在创建一个程序，它从食谱中刮取信息并将其放入数据库。我已经设法将菜谱的名称和URL插入到“菜谱”表中，并创建了一个充满配料的“项目”表。我现在正试图创建一个recipeItemAmount表，该表包含一个项目ID、它所在配方的配方ID和数量。但是，我不断收到一个错误，即当我插入唯一约束时，它失败了，但我没有在该数据库中实现唯一约束。代码如下：

def recipe_item_amount():
    '''
    Grab every web page in the BBC Food sitemap and
    save it as a local html file.
    '''

    # Cycle through the sitemap grabbing each recipe
    with open('bbc_sitemap.txt', 'r') as f:
        for line in f.readlines():
            db = create_connection("C:\\Users\\Eva Morris\\PycharmProjects\\pantry\\instance\\flaskr.sqlite")
            line = line.strip('\n')
            error = None
            filepath = os.path.join('BBC_Food_Repo', line.split('/')[-1] + '.html')

            # Don't get pages that have already been collected
            if not os.path.isfile(filepath):
                page = requests.get(line)

                try:
                    page.raise_for_status()
                except requests.RequestException:
                    continue

                soup = bs4.BeautifulSoup(page.text, 'html.parser')
                # Update the data file
                data = []
                for i in soup.find_all('li', class_='recipe-ingredients__list-item'):
                    data.append(i)

                for i, datum in enumerate(data):
                    if data[i]:
                        data[i] = datum.text.replace('\n', '')
                    else:
                        data[i] = ''

                name = [soup.find('h1', class_='content-title__text'),
                        ]

                for i, datum in enumerate(name):
                    if name[i]:
                        name[i] = datum.text.replace('\n', '')
                    else:
                        name[i] = ''

                title = name[0].strip('\"')

                ingredientAmount = 0
                ingredients = data[2:-1]
                for i in range(len(ingredients)):
                    ingredients[i] = ingredients[i].split()
                    for j in range(0, len(ingredients[i])-1):
                        if 48 <= ord(ingredients[i][j][0]) <= 57:
                            ingredientAmount = str(ingredients[i][j])
                        elif (
                                db.execute("SELECT itemID FROM item WHERE itemName = ?",
                                           (ingredients[i][j+1] + ' ' + ingredients[i][j] + '\n',)).fetchone()
                                is not None
                        ):
                            item = ingredients[i][j+1] + ' ' + ingredients[i][j] + '\n'
                        elif (
                                db.execute("SELECT itemID FROM item WHERE itemName = ?",
                                           (ingredients[i][j] + '\n',)).fetchone()
                                is not None
                        ):
                            item = ingredients[i][j] + '\n'
                        recipeID = db.execute('SELECT recipeID FROM recipe WHERE recipeName = (?)',(title,)).fetchone()[0]
                        itemID = db.execute('SELECT itemID FROM item WHERE itemName = (?)', (item,)).fetchone()[0]

                        db.execute('INSERT INTO recipeItemAmount VALUES (?, ?, ?) ', (recipeID, itemID, ingredientAmount))
                        db.commit()
                        break
            db.close()

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS item;
DROP TABLE IF EXISTS recipe;
DROP TABLE IF EXISTS recipeItemAmount;
DROP TABLE IF EXISTS pantry;

CREATE TABLE user (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL
);

CREATE TABLE pantry (
    itemID INT,
    quantity TEXT NOT NULL,
    FOREIGN KEY (itemID) REFERENCES item (itemID),
    PRIMARY KEY (itemID)
);

CREATE TABLE recipe (
    recipeID INTEGER PRIMARY KEY AUTOINCREMENT,
    recipeName TEXT UNIQUE,
    weblink TEXT UNIQUE
);

CREATE TABLE item (
    itemID INTEGER PRIMARY KEY AUTOINCREMENT,
    itemName TEXT NOT NULL,
    brand TEXT
);

CREATE TABLE recipeItemAmount(
    recipeID INT,
    itemID INT,
    quantity TEXT,
    FOREIGN KEY (recipeID) REFERENCES recipe(recipeID),
    FOREIGN KEY (itemID) REFERENCES item (itemID),
    PRIMARY KEY (recipeID, itemID)
)

以及错误消息：

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Program Files\JetBrains\PyCharm 2019.2.2\helpers\pydev\_pydev_bundle\pydev_umd.py", line 197, in runfile
    pydev_imports.execfile(filename, global_vars, local_vars)  # execute the script
  File "C:\Program Files\JetBrains\PyCharm 2019.2.2\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "C:/Users/Eva Morris/PycharmProjects/pantry/flaskr/BBCscraper/scraperecipes.py", line 202, in <module>
    recipe_item_amount()
  File "C:/Users/Eva Morris/PycharmProjects/pantry/flaskr/BBCscraper/scraperecipes.py", line 178, in recipe_item_amount
    db.execute('INSERT INTO recipeItemAmount VALUES (?, ?, ?) ', (recipeID, itemID, ingredientAmount))
sqlite3.IntegrityError: UNIQUE constraint failed: recipeItemAmount.recipeID, recipeItemAmount.itemID

Answer 1

主键是唯一的键PRIMARY KEY (recipeID, itemID)是recipeItemAmount的主键，因此这两个键一起必须是唯一的