相同数字数据类型的两个外键并将其引用到两个表 [英] two foreign keys to the same numeric data type and reference it to two tables

问题描述

可以为表中相同类型的数字数据创建两个外键，并将其引用到两个表中，我有这个例子

it´s possible create two foreign keys to the same type of numerical data in a table and reference it to two tables and I have this exemples in down

创建表管理员和主键

/* table admin*/
create table admin (id_admin number(10) not null, 
                    email_admin varchar(30) not null, 
                    password_admin varchar(10) not null);
/* primary key */
alter table admin add constraint admin_pk primary key (id_admin);

创建表用户和主键

/* table user*/
create table user (id_user number(10) not null, 
                   email_user varchar(30) not null, 
                   password_user varchar(10) not null);
/* primary key */
alter table user add constraint user_pk primary key (id_user);

创建表登录和主键外键

/* table login*/
create table login(id_login number(10) not null, 
                   id_admin_user_login number(10) not null, 
                   email_login varchar(20) not null, 
                   password_login varchar(10) not null);
/* primary key */
alter table login add constraint login_pk primary key (id_login);

/* foreign key reference to admin*/
alter table login add constraint login_fk_admin foreign key (id_admin_user_login) 
reference admin(id_admin);

 /* foreign key reference to user*/
alter table login add constraint login_fk_user foreign key (id_admin_user_login) 
reference user(id_user);

有可能吗?

推荐答案

您的数据模型似乎没有多大意义.您有三个不同的实体 admin、user 和 login.它们三个似乎都存储相同的信息——电子邮件地址、用户名和密码(我希望基本安全实际上是密码哈希).如果表之间存在任何关系，则违反基本规范化，因为您将在多个位置存储相同的信息.

Your data model doesn't seem to make a lot of sense. You have three different entities admin, user, and login. All three of them appear to store the same information-- an email address, a username, and a password (which I hope for basic security is really a password hash). If there are any relationships between the tables, that violates basic normalization because you'd be storing the same information in multiple places.

不知道您实际尝试建模的实体的业务需求，就很难准确地知道您想要什么.

Not knowing the business requirements for the entities you're actually trying to model, it is difficult to know precisely what you want.

我的第一个猜测是您有两种类型的用户，管理员和普通用户，每一种都可以登录到您的应用程序.假设用户的属性无论其角色如何都非常一致(管理员和普通用户都有电子邮件地址、密码等)，最简单的建模方法是使用单个 login 表和login_type 告诉您特定用户是管理员还是普通用户

My first guess is that you have two types of users, admins and regular users, each of which can log in to your application. Assuming that the attributes of users are pretty consistent regardless of their role (both admins and regular users have email addresses, passwords, etc.) the simplest way to model that would be with a single login table with a login_type that tells you whether a particular user is an admin or a regular user

create table login (
  login_id      integer primary key,
  email         varchar2(255),
  password_hash raw(32),
  login_type    varchar2(1) check( login_type IN ('A', 'U') )
);

您可以通过为您的 login 表引用的登录类型创建一个查找表来使其更加灵活

You can make that a bit more flexible by creating a lookup table for the login types that your login table references

create table login_type_lkup (
  login_type_code varchar2(1) primary key,
  login_type_desc varchar2(255)
);

create table login (
  login_id        integer primary key,
  email           varchar2(255),
  password_hash   raw(32),
  login_type_code varchar2(1) references login_type_lkup( login_type_code )
);

如果您想要更大的灵活性，下一步将是说登录并没有真正的类型.相反，他们拥有一个或多个具有某些权限的角色.您最初可能有一个 admin 角色和一个 regular user 角色，但后来想要添加一个 只读用户 角色，一个 superuser 角色等.在这种情况下，你会得到类似

If you want more flexibility, the next step would be to say that logins don't really have a type. Instead, they have one or more role that has some set of permissions. You might have an admin role and a regular user role initially but later want to add a read only user role, a superuser role, etc. In that case, you'd have something like

create table login (
  login_id        integer primary key,
  email           varchar2(255),
  password_hash   raw(32)
);

create table role (
  role_id integer primary key,
  role_desc varchar2(255)
);

create table permission (
  permission_id integer primary key,
  permission_desc varchar2(255)
);

create table login_role (
  login_id integer references login(login_id), 
  role_id  integer references role(role_id),
  primary key pk_login_role( login_id, role_id )
);

create table role_permission (
  role_id       integer references role(role_id),
  permission_id integer references permission(permission_id),
  primary key pk_role_permission( role_id, permission_id )
);

这篇关于相同数字数据类型的两个外键并将其引用到两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！