Database design whose table has a foreign key

Question

I have a table USER(USER_ID, PASSWORD, NAME, ...) and a table ACCESS_ROLESfor users, each user can have one ACCESS_ROLE(ONE-TO-ONE).

Is there a foreign key in the Wich table?
I would put a table USER_IDin a table ACCESS_ROLES. Is there any good practice approach?

Answer 1

Since you will have a one-to-one relationship, the solution suggested by Philip Kelly is better. Just add a new column in USERunder the name access_role_id, and then enter the foreign key in the table USERas follows:

ALTER TABLE USER ADD CONSTRAINT fk_access_roles_users
                 FOREIGN KEY (access_role_id) 
                 REFERENCES ACCESS_ROLES(access_role_id);

Do not add USER_IDto the table ACCESS_ROLESas you suggested.

Answer 2

If you have a fixed set of access roles and any number of users where one and only one access role is assigned to a user, and any number of users can be assigned a specified access rule [this is how I interpret your question], then you should put a column of type “AccessRoleId” to the USERS table and add a foreign key access restriction to ACCESS_ROLES.

Answer 3

• USER ( ) ACCESS_ROLE.
• ACCESS_ROLE

:

• FK - ACCESS_ROLE PK ACCESS_ROLES.
• FK- - USERS, FK - ACCESS_ROLE.

: () . ACCESSROLE, FK USERS ACCESSROLES

SQL Server

ALTER TABLE USERS WITH CHECK ADD
CONSTRAINT FK_USERS_ACCESS_ROLES FOREIGN KEY (ACCESS_ROLE) REFERENCES ACCESS_ROLES (ACCESS_ROLE /*PK?*/)

Answer 4

" ", , .

Answer 5

ACCESS_ROLES

.

UNIQUE (ACCESS_ROLES_ID, USER_ID)

Answer 6

- ?

, !

primary key, a foreign key constraint, .

foreign key . ( .)

, , , , , .

• foreign key primary key unique constraint, .
• A foreign key , , .
• primary key, foreign key NULL.

Answer 7

Not sure if I understand the question or not: I think access_table should have a foreign key.