• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How update and delete data from table which have a foreign key

 
Sarra Sakka
Ranch Hand
Posts: 283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have created with postgres three table : users ,roles and users_roles
as the followings :
table users :

table roles:

table users_roles:

the rolename can be if "admin" if "employee", it can be consider like a fixed table.
My problem is how to delete and update a username.
how can i solve this problem? thank you in advance
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should create surrogate primary keys for users and roles. That way your primary keys will be non-mutable, and you'll be able to update user and role names without problem.
 
Sarra Sakka
Ranch Hand
Posts: 283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:You should create surrogate primary keys for users and roles.

do you mean i remove the primary key both in users and roles and replace with a surrogate primary key?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, that's what I suggest.
 
Sarra Sakka
Ranch Hand
Posts: 283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But i want a unique user name not two or three when i used a surrogate key
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is what unique constraints are for. You can have several unique keys in the table. The primary key is just one, true, but for others, you'll just declare them UNIQUE.
 
Sarra Sakka
Ranch Hand
Posts: 283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You mean the other variable declared UNIQUE
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. Your table could look something like this:The syntax might be slightly incorrect, but the idea should be clear.

You might want to do some reading on database modelling. Searching for database modelling tutorial might perhaps give some good resources.

Also, you shouldn't ever store unencrytped passwords, if you really must, you should store a salted password hash (for security reasons - if someone stole your database, you don't want him to get the passwords on a silver plate). But this is another discussion, if you need advice on this, try some search or ask in the Security forum.
 
Sarra Sakka
Ranch Hand
Posts: 283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your effort
i will try to create my table as you tell me, and i will tell you the result
 
Sarra Sakka
Ranch Hand
Posts: 283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The id is not serial ?
what about roles table should i use a surrogate key also ?
and what about the users_roles what's the primary key
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic