1

Simple ER diagram

company(companytype) is a FOREIGN KEY in this case.

c_type(ID) is a Primary key then it will be automaticlly incremented. My question is : when the c_type(ID) will be incremented , will company(companytype) be automaticlly incremented too ?

When entering data, users are asked to select the company type, if it exists it will be used in the company table else a new one will have to be created.

Thanks in advance

e4c5
  • 48,268
  • 10
  • 82
  • 117
xAminex
  • 231
  • 2
  • 6
  • 16
  • Are you asking what will happen if an exisiting id in the c_type table is incremented? Just adding a new record to c_type should not effect the company table in anyway. – e4c5 Sep 13 '15 at 11:09
  • Ok thanks e4c5 eveny if I do MATCH FULL for the foreign key ? – xAminex Sep 13 '15 at 11:12
  • What exactly are you trying to achieve? – e4c5 Sep 13 '15 at 11:19
  • I would like that the user fills a formular : -companyname... -companyfield... and when it comes to the companytype , he has to add a type on his own, then I would like that the foreign key in this case compnay(companytype) takes automatically the c_type(ID) value, just to save th information correctly. – xAminex Sep 13 '15 at 11:28
  • You are still not being clear. If each company has it's own type why do you need two tables? – e4c5 Sep 13 '15 at 11:30
  • cos next time when another user fills the formular he can use the type in the existing table without typing it again – xAminex Sep 13 '15 at 11:33
  • If you have concurrent access, consider: [Is SELECT or INSERT in a function prone to race conditions?](http://stackoverflow.com/a/15950324/939860). – Erwin Brandstetter Sep 13 '15 at 13:15
  • Thanks Erwin Brandstetter – xAminex Sep 13 '15 at 14:45

2 Answers2

1

There is no question of company(companytype) being auto incremented. What you need is an autoincrement (SERIAL) field for id in the c_type table. When the user specifies the company type, you need to insert into the c_type table and user the autogenerated id in the company table as the value for companytype field.

If you want subsequent edits in the c_type table to be reflected there, you would need to the ON UPDATE CASCADE

e4c5
  • 48,268
  • 10
  • 82
  • 117
  • Thanks e4c5 I will try it, but in the Postgresql documentation I found this "CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. " It's true only for deleting or for inserting too – xAminex Sep 13 '15 at 12:06
  • Occupational hazard of using foreign keys! to avoid the deletion use ON DELETE NO ACTION (which is the default) – e4c5 Sep 13 '15 at 12:09
  • Thanks e4c5 I didn't try it yet but I'll mark it as solved. I'm a beginner, If you can give me a document to learn more about UPDATE CASCADE and DELETE RESTRICT and such options that'll be nice of you. Thanks in advance – xAminex Sep 13 '15 at 12:17
  • psotgresqltutorial.com is a good place for many topics on it here is the one for forign keys http://www.postgresqltutorial.com/postgresql-foreign-key/ – e4c5 Sep 13 '15 at 12:20
0

In short NO ! Foreign Key are used for referencing to another table primary key !

boyshot17
  • 39
  • 3
  • That mean I have to add a new record in c_type first then copy ID on companytype ? – xAminex Sep 13 '15 at 11:14
  • Yes, there need to be something for Foreign Key to refer to ! You cannot input companytype value that is not present in c_type.ID . this might help. http://www.w3schools.com/sql/sql_foreignkey.asp – boyshot17 Sep 13 '15 at 11:22