• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Tim Cooke
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • paul wheaton
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Tim Holloway
  • Carey Brown
  • salvin francis

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

 
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
 
Sheriff
Posts: 3752
62
Netbeans IDE Oracle Chrome
  • 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
Netbeans IDE Oracle Chrome
  • 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
Netbeans IDE Oracle Chrome
  • 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
Netbeans IDE Oracle Chrome
  • 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
 
That new kid is a freak. Show him this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!