Is it possible to create a table where two columns represent a primary key and one of them is autonumber but not distinct. Something like (teacherID,schoolID(FK),teacherName) where first two are primary key and first one is autonumber. When teachers from different schools are inserted the teacherID autoincrement should depend from school.
Example for insertion
I know it's possible to make SQL query to insert like this, but is it possible in table settings?
You said this: Something like (teacherID,schoolID(FK),teacherName) where first two are primary key and first one is autonumber.
It sounds like you have a many to many relationship between teachers and schools. That's fairly normal. You accomplish this with 3 tables - teacher, school, and a junction table. Let's call that teacherSchool. It would contain foreign keys to the other 2 tables, plus whatever else you want to store (isActive, dateCreated, etc). It's primary key would be the two foreign keys plus whatever else you need to make it unique.
To enter records into the teacherSchool table, you have to pass your insert query the necessary values. This would be incorporated into whatever you are using for data entry. In fact, even with your current design, you need something that allows your data entry person to identify the school. Whatever that is should pass the schoolID to your insert query.
That's not how autonumbers work. If you want to do something like this, you will have to calculate it yourself. But I have to caution you against this. I assume this is a teacher table and what you want to do is a bad idea because
