Sql Database table with Primary key

[Mohan Giri]

hi everyone,
Can i have a primary key for more than fields in a sql server database table? If I can tell me the query syntax for alter my existing table with more than one primary key.

 

[brady]

no, you can only have 1 primary key.

 

[Mohan Giri]

If it is Oracle Database can I have primary key for more than one column???

 

[brady]

no, you can only have 1 primary for each column.

 

[Mohan Giri]

ok. thanks bradybnmci

 

[brady]

you're welcome

 

[fitzjj]

in mysql you can have composite keys - primary keys made up of multiple fields

 

[Mohan Giri]

How can I have. Could you please explain little brief???

 

[Mohan Giri]

Thanks for your timely help. I got it. In SQL also I can have.

Syntax are:

Using a CREATE TABLE statement

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n))

Using an ALTER TABLE statement

ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)

Thanks.

 

[fitzjj]

How can I have. Could you please explain little brief???

In order to have a composite key you just list multiple fields in your primary key like you have done here

PRIMARY KEY (column1, column2, . column_n))

although what is here is VERY bad practice. A primary key should identify a single record in a table. for example if every student in a university database had a unique ID then student ID would be a suitable primary key for the student table.

PRIMARY KEY(studentID)

If the student table also had a first name and last name field then it would not be acceptable to use something like

PRIMARY KEY(firstName)

PRIMARY KEY(lastName)

or even

PRIMARY KEY(firstName, lastName)

because none of these are unique, it is entirely possible for someone to have the same name as another student.

What you have done in your code above is effectively the same as doing:

PRIMARY KEY(studentID, firstName, lastName)

when clearly the first name and surname, although when combined with studentID do combine to make a primary key, are not neccessary since studentID on its own can be used as a primary key.

If you are using column1, column2,.... colum_n as a primary key then there must be a combination of less than n fields that give a unique identifier for that record. You should never need to use n fields in a primary key and should when possible keep the number of fields in the primary key to as few as possible. Partly because it is bad practice and inefficient not to, but also because it will be a b1tch when it comes to relating carrying out queries on your table if you have to enter n fields to specify a record. If there is no unique identifier then just add one, there is no harm in adding a single field to the beginning of a table - the end user doesnt even need to know that it is there, it could auto increment itself meaning it will always be unique.