时间戳

首页 > 解决方案 > SQL Server - INSERT 语句与 FOREIGN KEY 约束冲突

sql - SQL Server - INSERT 语句与 FOREIGN KEY 约束冲突

问题描述

CREATE TABLE collections 
(
    id INTEGER IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE posts 
(
    id INTEGER IDENTITY(1, 1) PRIMARY KEY,
    title VARCHAR(50) NOT NULL,
    image_URL VARCHAR(120) NOT NULL,
    author_id INTEGER NOT NULL,
    collection_id INTEGER,

    FOREIGN KEY(author_id) REFERENCES users(id),
    FOREIGN KEY(collection_id) REFERENCES collections(id)
);

INSERT INTO collections (name) 
VALUES ('Travel'), ('Nature'), ('Great Outdoors');

INSERT INTO posts (title, image_url, author_id, collection_id) 
VALUES ('White Mountains', 'cloudinary.com/snp/balhwpq.jpg', 3, NULL), 
       ('My new car!', 'cloudinary.com/snp/qpcnalpw.jpg', 1, NULL), 
       ('Great weekend for camping', 'cloudinary.com/snp/nmclapa.png', 5, 3), 
       ('Train Ride to France', 'cloudinary.com/snp/rjaptpalw.jpg', 4, 1), 
       ('Small Town in Italy', 'cloudinary.com/snp/aqypqoua.jpg', 4, 1), 
       ('Nice Day At The Air Show', 'cloudinary.com/snp/aswpqlsaq.jpg', 7, NULL), 
       ('Barcelona Beach', 'cloudinary.com/snp/quapwnla.jpg', 9, NULL), 
       ('Gorgeous City of Prague', 'cloudinary.com/snp/anvmapw.png', 3, NULL),
       ('Nothing like fresh Seafood!', 'cloudinary.com/snp/uqnqhapla.jpg', 8, 4), 
       ('Bought a new Swiss army knife', 'cloudinary.com/snp/qanvpauaq.jpg', 6, NULL), 
       ('Delicious meal in Boston', 'cloudinary.com/snp/malvpqlsa.jpg', 10, NULL),
       ('Swimming at Ursa Beach', 'cloudinary.com/snp/poqlanoqlav.jpg', 12, NULL), 
       ('Chilly day in Norway', 'cloudinary.com/snp/vqwlnqposp.jpg', 11, NULL), 
       ('Hiking through beautiful Vermont!', 'cloudinary.com/snp/qpoplnalqi.jpg', 5, 3);

每次我尝试执行此操作时，它都会完成表创建和前两个插入语句，然后出现错误

INSERT 语句与 FOREIGN KEY 约束冲突

我认为问题在于，在帖子表 Insert 语句中，我将一些collection_id值设置为 NULL，这会导致错误。我从来没有说过这个列不能为NULL，那有什么问题呢？

标签： sqlsql-server

解决方案

推荐阅读