>> (what is the difference between a column and a field in SQL terms?) <<
Read the ANSI/ISO Standard SQL document, A field in SQL is part of a temporal value -{YEAR, MONTH, DAY, HOUR MINUTE, SECOND} , a very specific meaning in the language. But more than that, a field is physical storage while a column is logical. A column can
be virtual or computed and have no physical existence at all. Or the column can be in records on a RAID drive and appear in many physical locations. Fields have no defaults , no constraints, no DRI and any data type is assigned in the application
layer by the program reading it.
>> Considering a very basic table - <<
You also do not know ISO-11179 rules or basic data modeling. A table models a set, so its name is a plural or collective noun. Your mindset is still back in file systems. They work with one record at a time, which models a single employee, like you did. What
math do you do with an employee identifier? None! So it should not be a numeric. The USPS standard for address lines is 35 Characters (this is based on a 3.5 label with 10-pitch type). Leaving out the usual CHECK() stuff, is this what you meant?
CREATE TABLE Personnel -- see the level of abstraction?
(emp_id CHAR(10) NOT NULL PRIMARY KEY, -- industry or government id
emp_name VARCHAR(35) NOT NULL,
emp_address_1 VARCHAR(35) NOT NULL,
emp_address_2 VARCHAR(35) NOT NULL,
city_name VARCHAR(2) NOT NULL
state_code CHAR(2) NOT NULL.
zip_code CHAR(5) NOT NULL);
>> I want to be able to generate emp_id every time a new employee row (record?) is added to the table.
(what is the difference between a row and a record as per you?) <<
Logical versus physical storage again. Google around for an old article of mine with a few thousand words on this. And you do not want to generate an identifier; you want to discover it. The entity already exists, so unless you are making synthetic personnel
and have to assign a serial numbers, it should be there already. In the case of personnel, you will need a tax number or other legal identifier. You do not want to tell the government that you sequentially number your employees, based on when they are hired
at this location!
>> Are you suggesting I do the following every time I have to generate a new emp_id value ? -
SELECT MAX(COALESCE (emp_id, 0)) + 1 FROM Personnel; <<
A key is a subset of attributes of an entity which is unique. Uniqueness is just one property of a key. But this is just math on am incorrectly designed column. Ugh! Your mindset is still back the 1960's and pointer chains that serve a physical locators for
the data on one disk on one machine. IDENTITY is the old Sybase/UNIX record numbering, not RDBMS.
RDBMS is a whole different model of programming and data.