时间戳

首页 > 解决方案 > MS SQL 中的外键引用无效列错误

sql - MS SQL 中的外键引用无效列错误

问题描述

这一切都在 MS SQL 中。

我已经看到这个问题弹出了很多，我四处搜索，但我仍然无法弄清楚我的问题。我收到错误消息，告诉我我在未创建表的无效列中的外键引用。但是，我创建了该表，因为它显示在我的数据库表列表中，因此它显示它已创建。

我试图将该表移到引用它的表上方，但我仍然收到错误消息。有人知道如何解决这个问题吗？

我的代码：

CREATE SCHEMA usr_in;
go

CREATE TABLE gender_interst (
    id int IDENTITY (1,1),
    gend_id int
    CONSTRAINT gender_interstpk PRIMARY KEY (id)
)

CREATE TABLE gender (
id int IDENTITY (1,1),
gend VARCHAR (20)
CONSTRAINT genderpk PRIMARY KEY (id)
);

SELECT * FROM gender_interst LEFT JOIN gender on gender_interst.id = gender.id;

SELECT * from gender_interst;

CREATE TABLE user_info (
    id int PRIMARY KEY IDENTITY (1,1),
    usr_name VARCHAR (30) NOT NULL,
    f_name VARCHAR (30) NOT NULL,
    l_name VARCHAR (30),
    b_day DATE,
    email VARCHAR  (120) NOT NULL,
    genderpkid VARCHAR (10) NOT NULL,
     CONSTRAINT gender_fk FOREIGN KEY (genderpkid) REFERENCES gender (genderpk),
);


INSERT
  INTO user_info 
     ( usr_name, f_name, l_name, b_day, genderpkid, email)
VALUES 
     ('JMAN', 'JOHN', 'DOE', '1990-01-01','M','EX@EMAIL'),
     ('JAM','JANE', 'DOE', '1995-05-02','F','EX@EMAIL'),
     ('NMAN','NICK', 'WEBB', '1999-06-22','M','EX@EMAIL'),
     ('LOBA','LOLA', 'LILLY', '1994-01-08','F','EX@EMAIL'),
     ('NOTSPMAN','PETER', 'PARKER','1985-11-25','M','EX@EMAIL');

SELECT * FROM user_info;

标签： sql

解决方案

您的外键需要引用表列的名称（或者是idor gend）而不是主键的名称 ( genderpk)。

因此外键脚本 ( CONSTRAINT gender_fk FOREIGN KEY (genderpkid) REFERENCES gender (genderpk)) 应该类似于CONSTRAINT gender_fk FOREIGN KEY (genderpkid) REFERENCES gender (id)

但是，要创建关系，两列需要具有相同的数据类型。user_info .genderpkid是VARCHAR (10)和user_info.id是int。

您可能遇到的另一个问题是插入脚本将数据以或的形式插入到genderpkid列中。因此 int 不起作用MF

如果您要使用值 M 或 F 来表示性别，那么您可以创建如下脚本：

CREATE TABLE gender (
    id VARCHAR (1),
    gender VARCHAR (20)
    CONSTRAINT gender_pk PRIMARY KEY (id)
);

CREATE TABLE user_info (
    id int PRIMARY KEY IDENTITY (1,1),
    usr_name VARCHAR (30) NOT NULL,
    f_name VARCHAR (30) NOT NULL,
    l_name VARCHAR (30),
    b_day DATE,
    email VARCHAR  (120) NOT NULL,
    genderId VARCHAR (1) NOT NULL,
    CONSTRAINT gender_fk FOREIGN KEY (genderId) REFERENCES gender (id),
);

INSERT INTO gender (id, gender)
VALUES 
('F', 'Female'),
('M', 'Male')

INSERT
  INTO user_info 
     ( usr_name, f_name, l_name, b_day, genderId, email)
VALUES 
     ('JMAN', 'JOHN', 'DOE', '1990-01-01','M','EX@EMAIL'),
     ('JAM','JANE', 'DOE', '1995-05-02','F','EX@EMAIL'),
     ('NMAN','NICK', 'WEBB', '1999-06-22','M','EX@EMAIL'),
     ('LOBA','LOLA', 'LILLY', '1994-01-08','F','EX@EMAIL'),
     ('NOTSPMAN','PETER', 'PARKER','1985-11-25','M','EX@EMAIL');

SELECT * FROM user_info;

更好的方法可能是将整个短语（男性/女性）传递给桌子user_info。该表gender可用于强制执行referential integrity。然后，您的所有信息都包含在SELECT * FROM user_info

例如：

  CREATE TABLE gender (
        [name] VARCHAR (20)
        CONSTRAINT gender_pk PRIMARY KEY ([name])
    );
    
    CREATE TABLE user_info (
        id int PRIMARY KEY IDENTITY (1,1),
        usr_name VARCHAR (30) NOT NULL,
        f_name VARCHAR (30) NOT NULL,
        l_name VARCHAR (30),
        b_day DATE,
        email VARCHAR  (120) NOT NULL,
        gender VARCHAR (20) NOT NULL,
        genderIntrest VARCHAR (20) NOT NULL,
        CONSTRAINT gender_fk FOREIGN KEY (gender) REFERENCES gender ([name]),
        CONSTRAINT genderIntrest_fk FOREIGN KEY (genderIntrest) REFERENCES gender ([name]),
    );
    
    INSERT INTO gender ([name])
    VALUES 
    ('Female'),
    ('Male')
    
    INSERT
      INTO user_info 
         ( usr_name, f_name, l_name, b_day, gender, genderIntrest, email)
    VALUES 
         ('JMAN', 'JOHN', 'DOE', '1990-01-01','Male','Female', 'EX@EMAIL'),
         ('JAM','JANE', 'DOE', '1995-05-02','Female','Female', 'EX@EMAIL'),
         ('NMAN','NICK', 'WEBB', '1999-06-22','Male','Female','EX@EMAIL'),
         ('LOBA','LOLA', 'LILLY', '1994-01-08','Female','Female','EX@EMAIL'),
         ('NOTSPMAN','PETER', 'PARKER','1985-11-25','Male','Female','EX@EMAIL');
    
    SELECT * FROM user_info;

您可以删除性别表并让使用它的应用程序传入数据。但是对于学习练习，最好将其留在

推荐阅读