时间戳

首页 > 解决方案 > 创建和插入具有多对多关系的数据

sql - 创建和插入具有多对多关系的数据

问题描述

我在postgresql中尝试了一对多的关系，但没有

这是我的错误尝试

CREATE TABLE person_basic_info (
    id                                    INT           NOT NULL PRIMARY KEY,
    gender                                VARCHAR (50)  NULL,
    first_name                            VARCHAR (150) NULL,
    last_name                             VARCHAR (150) NULL,
    email                                 VARCHAR (50)  NULL,
    political_view_id                     INT           NULL,
    cambridge_analytica_psychographics_id INT           NULL REFERENCES persons_features (id),
    revolution_sympathy                   int           NULL,
    iq_level                              INT           NULL
);

创建人物特征

CREATE TABLE persons_features (
    id               INT            NOT NULL PRIMARY KEY,
    dominate_feature VARCHAR (100) NULL
);


--person_basic_info

insert into person_basic_info(id, gender, first_name, last_name, email, political_view_id, cambridge_analytica_psychographics_id,revolution_sympathy,iq_level) values (1,'Female','Corenda','Garrood','cgarrood0@yellowbook.com',6,2,0,86);
insert into person_basic_info(id,gender, first_name, last_name, email, political_view_id, cambridge_analytica_psychographics_id,revolution_sympathy,iq_level) values (2,'Male','Langston','McMychem','lmcmychem1@theatlantic.com',2,4,0,111);
insert into person_basic_info(id,gender, first_name, last_name, email, political_view_id, cambridge_analytica_psychographics_id,revolution_sympathy,iq_level) values (3,'Female','Robbyn','Imison','rimison2@geocities.com',14,3,1,98);


--persons_features
insert into persons_features (id, dominate_feature) values (1,'Agreeableness');
insert into persons_features (id, dominate_feature) values (2,'Conscientiousness');
insert into persons_features (id, dominate_feature) values (3,'Extraversion');
insert into persons_features (id, dominate_feature) values (4,'Neuroticism');
insert into persons_features (id, dominate_feature) values (5,'Openness');

但什么都没有。

你可以帮帮我吗？

标签： sqlpostgresqlinsertcreate-table

解决方案

如果您以正确的顺序执行此查询，您将不会收到任何错误。在这种情况下，正确的顺序应该是：

  1. 创建表persons_features
  2. 创建表 person_basic_info
  3. 插入persons_features
  4. 插入 person_basic_info

在这种情况下，这不是多对多关系，除非 person 可以有多个 的值cambridge_analytica_psychographics_id。如果是这样，您应该创建交叉表而不是引用persons_features表。

推荐阅读