How to select primary key from given table columns?

Question

If you have 4 columns in your table as(First Name, Last Name, Salary, Department) then which column would be most suitable to make as primary key and why would you choose that.
Basically I just wanted to know how it should be done or is there anything related to foreign key in this situation.

Answer 1

You should make one Extra Column Name ID and that should be the primary key.

That key could be used also as Surrogate Key when required in your Scripting.

Answer 2

Like Everton said. Nothing in the columns you have will always have unique values and as such are useless as primary keys.

Answer 3

salary will be bad too. you will not be able to have two people on the same salary in the whole company.

Answer 4

you could go primary on surname and foriegn on department.. not great but it should work as long as two people with the same last name will never be in the same department

Answer 5

I suggest make a Super Key of First Name and Last Name that acts as Primary Key
I hope that works………

Answer 6

If a key on all 4 isn't practical just to move on, some people insert a numeric (or alpha) identifier and extract it later (stored procedure or view often easiest). To retain sorting it's zero-padded, e.g.:

Jones_001 Bob
Jones_002 Bob
Jones_A Bob

It's dirty but effective. The catch is shuffling that number around.

If salary contains a decimal (like 10000.00), you could use digits past the significant ones (I think this could be messier):

10000.00001
10000.00002

Answer 7

I suggest you create an integer column for a primary key; unique. You can also use a combination of statements to create an a-zA-Z0-9 value.

Answer 8

its pretty good english. wish i could speak portuguese half as good as you speak english lol

Answer 9

mmhmmm we suggested this. think his boss said no.

Answer 10

if i had to choose, i would choose to create a new identity column, all these columns can have equal results and you're not going to want this.
PS: Sorry for my bad english, i'm brazilian!

Answer 11

then salary is the best column, i guess... Because salary is the column that can most change. You can have persons with the same first name or last name, and i think it's not just one person per department.

Answer 12

the jay's solution should work for now, but whenever possible use an identity column for this cases!

Answer 13

Primary key is a column or a set of columns what can be used to uniquely identify a "single" row in the table. In another words, primary key is an identifier for a record like the social security number is an identifier for a person in US.

Now, Given your four columns there were no such column that can be used as primary key. Because Two persons can have same first name and last name. But if are sure enough that in practice it won't happen (depends on your domain/business) then you can use both as primary key.

Alternatively, create an id column and give it an integer to identity a single person/record. That is the standard usage today

Answer 14

best way to learn relational databases is to see the example of northwind in Microsoft access

Answer 15

make a simple ID and its type Int ( increamental) and no duplicates and not null

Answer 16

a sql statement might look like this

Select LastName, FirstName, age from tblDemographics where
ID= 5 ;

so with one id you can get all the columns or as many as you want
for all columns use
Select * from tblEmployees where EmployeeID =8;

Answer 17

Since you said you can't add columns, and if you're lucky enough to be using postgresql for your sql here. Postgresql has an innate primary key called oid. you can do SELECT oid, * FROM table. Keep in mind that oid eventually wraps so its not exactly a PK with large tables. If you're using anything else your best bet is to make it a multi column PK constraint that uses all 4 columns. Add "CONSTRAINT pk_id PRIMARY KEY (First name, last name, salary, department)". But as pointed out by everyone, in a real world situation this does not work because people have the same name could work in the same department and make the same amount. So even that 4 column PK could be non-unique. Create a real PK column like id when possible.

Answer 18

it's better to have another column as ID. after adding that column you can give the data type as int. then you can goto properties of the rable which is at the bottom in visual basic 2010. then you can give the auto increment property for it by making identity specifiation "yes". then by right clicking the column name,you can set the primary may for it.

It's not sutable to set names or anyother strings as primary key(PK),because that data can be duplicate. when we set those as PK you could not be able to add duplicate values for it. PK never allows to duplicate values and even if not allows the null values.that's why we made the ID as an auto increment.

Answer 19

hahaha i'm learning, but thank you!

Answer 20

sorry additional column is not allowed then I have to crack this problem