时间戳

首页 > 解决方案 > SQL 创建和填充

sql-server - SQL 创建和填充

问题描述

任何有经验的人都可以快速浏览以下内容。我还不够聪明，无法解决系统错误。


CREATE TABLE TestSubject (

test_subjectID      NCHAR (6)   NOT NULL PRIMARY KEY,
subject_name            NVARCHAR (10)   NOT NULL,
subject_address     NVARCHAR (12)   NOT NULL,
)
;
CREATE TABLE PhNumber (

subject_id      NCHAR (6)   NOT NULL,
ph_number       NVARCHAR (20)   NOT NULL,

PRIMARY KEY (ph_number),
FOREIGN KEY (subject_id) REFERENCES TestSubject (test_subjectID)
);


CREATE TABLE Test (

test_date       DATETIME    NOT NULL,
subject_id      NCHAR (6)   NOT NULL,
result          NVARCHAR (10),

PRIMARY KEY (test_date),

FOREIGN KEY (subject_id) REFERENCES TestSubject (test_subjectID));

CREATE TABLE Place (

place_name      NVARCHAR (10)   NOT NULL PRIMARY KEY,
place_address       NVARCHAR (8),
place_owner     NVARCHAR (8)
);

CREATE TABLE VisitSubject (

v_date          DATETIME    NOT NULL,
v_number        NVARCHAR (20)   NOT NULL,
place_visit     NVARCHAR (10)   NOT NULL,
party_size      INT,

PRIMARY KEY (v_date),

FOREIGN KEY (v_number) REFERENCES PhNumber (ph_number),
FOREIGN KEY (place_visit) REFERENCES Place (place_name));

和填充脚本

INSERT INTO TestSubject
VALUES 
('S001', 'Daniel Doppler', '5 Beetle Avenue Aberdeen'),
('S002', 'Florence West', '17 Green Crescent Dundee'),
('S003', 'Werner Flick', '25A Grubb Street Stonehaven'),
('S004', 'Tiffany Smith', '11 Green Crescent Dundee'),
('S005', 'Angela Ashe', '113 Wasp Street Aberdeen');


INSERT INTO Test
VALUES
('2020/08/05 00:00:00', 'S001', 'Negative'),
('2020/08/10 00:00:00', 'S002', 'Positive'),
('2020/08/17 00:00:00', 'S003', 'Negative'),
('2020/08/22 00:00:00', 'S004', 'Negative'),
('2020/08/22 00:00:00', 'S005', 'Negative'),
('2020/09/03 00:00:00', 'S001', 'Positive');

INSERT INTO PhNumber
VALUES
('S001', '07123-123456'),
('S002', '07777-111000'),
('S003', '07555-246810'),
('S004', '07101-484848'),
('S004', '07896-102304'),
('S005', '07777-534242');

INSERT INTO Place
VALUES
('Pink Lion Inn', 'Forfar', 'Sean Conran'),
('Irenes Hair Salon', 'Stonehaven', 'Irene Jones');

INSERT INTO VisitSubject
VALUES
('2020/08/01 00:00:00', '07123-123456', 'Irenes Hair Salon', '1'),
('2020/08/01 00:00:00', '07777-111000', 'Irenes Hair Salon', '2'),
('2020/08/25 00:00:00', '07123-123456', 'Pink Lion Inn', '4'),
('2020/08/25 00:00:00', '07493-285113', 'Pink Lion Inn', '2'),
('2020/09/10 00:00:00', '07555-246810', 'Irenes Hair Salon', '1'),
('2020/09/15 00:00:00', '07555-246810', 'Pink Lion Inn', '3');

由于 2 个错误，我无法填充现有表 -

字符串或二进制数据将被截断

和

INSERT 语句与 FOREIGN KEY 约束“FK__Test__subject_id__286302EC”冲突。冲突发生在数据库“TAT_2012552”、表“dbo.TestSubject”、列“test_subjectID”中。

INSERT 语句与 FOREIGN KEY 约束“FK__PhNumber__subjec__25869641”冲突。冲突发生在数据库“TAT_2012552”、表“dbo.TestSubject”、列“test_subjectID”中。

标签： sql-serverdatedatetime

解决方案

第一个错误意味着您试图在数据类型长度小于该值的列中插入一个值。例如，在您的VisitSubject表中，该place_visit列可以接受不超过 10 个字符的字符串。例如，在您的情况下，无法插入值Irenes Hair Salon ，因为它超过 10 个字符。

第二个错误被抛出，因为subject_id值插入到您的表中Test，并且在表PhNumber中不存在TestSubject。为什么它不存在于您的表中TestSubject？因为 SQL 无法在此表中插入任何行，因为插入subject_name的subject_address值具有比表中定义的数据类型长度更多的字符。

请先解决插入值比数据类型定义长的问题，然后它应该解决您的外键问题。

推荐阅读