Sql combine value of two columns as primary key

Question

I have a SQL server table on which I insert account wise data. Same account number should not be repeated on the same day but can be repeated if the date changes.

The customer retrieves the data based on the date and account number.

In short the date + account number is unique and should not be duplicate.

As both are different fields should I concatenate both and create a third field as primary key or there is option of having a primary key on the merge value.

Please guide with the optimum way.

Answer 1

You can create a composite primary key. When you create the table, you can do this sort of thing in SQL Server;

CREATE TABLE TableName (
  Field1 varchar(20),
  Field2  INT,
  PRIMARY KEY (Field1, Field2))

Take a look at this question which helps with each flavour of SQL

How can I define a composite primary key in SQL?

Answer 2

PLEASE HAVE A LOOK, IT WILL CLEAR MOST OF THE DOUBTS !

We can state 2 or more columns combined as a primary key.

In that case every column included in primary key will be called : Composite Key

And mind you Composite keys can never be null !!

Now, first let me show you how to make 2 or more columns as primary key.

create table table_name ( col1 type, col2 type, primary key(col1, col2));

The benefit is :

1. col1 has value (X) and col2 has value (Y) then no other row can have col1 as (X) and col2 as (Y).

2. col1, col2 must have some values, they can't be null !!

HOPE THIS HELPS !

Answer 3

Not at all. Just use a primary key constraint:

alter table t add constraint pk_accountnumber_date primary key (accountnumber, date)

You can also include this in the create table statement.

I might suggest, however, that you use an auto-incrementing/identity/serial primary key -- a unique number for each row. Then declare the account number/date combination as a unique key. I prefer such synthetic primary keys for several reasons:

• They make it easy to refer to a row in foreign key relationships.
• They show the insert order into the table, so you can readily see the last inserted rows.
• They make it simple to identify a single row for updates and deletes.
• They hide the "id" information of the row from referring tables and applications.

Answer 4

The alternative is to have a PK which is an autoincrementing number and then put a unique unique index on the natural key. In this way uniqueness is preserved but you have the fastest possible joining to any child tables. If the table will not ever have child tables, the composite PK is a good idea. If there will be many child tables, this is could be a better choice.