Date as primary key?

[debasisdas]

Um... in this case, using the date alone as the PK is probably not a good idea. Since the PK must be unique, it means you can only store one record per date. And I don't think this is what you want.

To avoid this both id and date must me marked as composit primary key.

so that id can be duplicate ,date can be duplicate but the combination of both can't be.

[Vbbeginner07]

To avoid this both id and date must me marked as composit primary key.
so that id can be duplicate ,date can be duplicate but the combination of both can't be.

im rite now using sql server where i kept both as a primary key....making errors?

[lotus18]

To avoid this both id and date must me marked as composit primary key.

so that id can be duplicate ,date can be duplicate but the combination of both can't be.

I have a little knowledge in database, but can you explain to me what does the composite primary key mean? I think it has been discussed to us by our professor but I think I wasn't listening at that time.

[debasisdas]

Composite primary key means more than one field combination works as the primary key. In Oracle a maximum of 32 key combination can be used as composite key.

[Vbbeginner07]

Composite primary key means more than one field combination works as the primary key. In Oracle a maximum of 32 key combination can be used as composite key.

how about sql server???

[Killer42]

Keep in mind, it's not some special type of field called a "composite primary key". The word "composite" refers to any key (or "index") which is made up of more than one field.

If you want the database software to automatically prevent record duplication, you need to define a "unique key". I think the meaning of this one is fairly obvious - it only allows unique values in that key. So a record which would produce a duplicate key value cannot be stored.

Any key may be defined as unique, regardless of whether it is the primary one, and regardless of how many fields are used to build it.

But I'm pretty sure that by definition, the primary key is always unique.

[QVeen72]

Hi Lotus,

"Composite Primary Key", means in a Table, Primary Key is composed of more than one Column (all together )..
Say in the above Example ,
Date + ID is Composite PK, Like Same Date Can Have Many ID's and Same ID can have Many Dates, but when combined together, there is only one Unique Record:

Date ID
01-01-2008 001
01-01-2008 002
01-01-2008 003
01-01-2008 004

02-01-2008 001
03-01-2008 001
04-01-2008 001
05-01-2008 001


Regards
Veena

[Vbbeginner07]

Hi Lotus,

"Composite Primary Key", means in a Table, Primary Key is composed of more than one Column (all together )..
Say in the above Example ,
Date + ID is Composite PK, Like Same Date Can Have Many ID's and Same ID can have Many Dates, but when combined together, there is only one Unique Record:

Date ID
01-01-2008 001
01-01-2008 002
01-01-2008 003
01-01-2008 004

02-01-2008 001
03-01-2008 001
04-01-2008 001
05-01-2008 001


Regards
Veena

any meansd for sql server?

[QVeen72]

any meansd for sql server?

Hi,

You can create Composite PK in SQL Server also.
Create table syntax is :

[code=oracle]
CREATE TABLE MYTABLE (
TDATE DATE NOT NULL,
ID INTEGER NOT NULL,
ENAME VARCHAR(50),
PRIMARY KEY (TDATE, ID)
)

[/code]

Regards
Veena

[lotus18]

Hi Lotus,

"Composite Primary Key", means in a Table, Primary Key is composed of more than one Column (all together )..
Say in the above Example ,
Date + ID is Composite PK, Like Same Date Can Have Many ID's and Same ID can have Many Dates, but when combined together, there is only one Unique Record:

Date ID
01-01-2008 001
01-01-2008 002
01-01-2008 003
01-01-2008 004

02-01-2008 001
03-01-2008 001
04-01-2008 001
05-01-2008 001


Regards
Veena

OK. I got it. Very well explained Veena. To Debasisdas and Killer42, thank you also. From now on, I am going to pay attention to our class.

[Vbbeginner07]

OK. I got it. Very well explained Veena. To Debasisdas and Killer42, thank you also. From now on, I am going to pay attention to our class.

code( vb)
If Val(Form2.txtta mt.Text) = 0 Then
MsgBox "Sorry,Ur Not Allowed To Do So!!!", vbOKOnly, "ERROR"
Exit Sub
Else
strName = Trim(ListView1. SelectedItem)
strId = ListView1.Selec tedItem.SubItem s(1)
rs.Open "select * from empdetail Where name = '" & strName & "'", conn, adOpenStatic, adLockOptimisti c
store = rs!id
rs.Close
Set rs = Nothing
rs1.Open "select * from saldetail where id = '" & store & "'", conn, adOpenStatic, adLockOptimisti c
storDate = rs1!Date
storId = rs1!id
If rs1.EOF Then
If Form2.dtpay.Val ue = "'" & storDate & "'" And strId = "'" & store & "'" Then
MsgBox "duplicate entry"
Else
conn.Execute "insert into saldetail(id,da te,othours,otam t,taxrate,netsa l,province)valu es ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
rs1.Close
Set rs1 = Nothing
End If
End If
End If


code using composit key??? i am not gettng it........used with sql server and vb???

[QVeen72]

Hi,

First Create Composite PK (ID + Date), and change last part of your coding ...:

[code=vb]
rs1.Open "select * from saldetail where id = '" & store & "' and Date = '" & dtPay & "'", conn, adOpenStatic, adLockOptimisti c
If Not rs1.EOF Then
MsgBox "duplicate entry"
Else
conn.Execute "insert into saldetail(id,da te,othours,otam t,taxrate,netsa l,pro vince)values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
rs1.Close
Set rs1 = Nothing
End IF
[/code]

Regards
Veena

[Vbbeginner07]

Hi,

First Create Composite PK (ID + Date), and change last part of your coding ...:

[code=vb]
rs1.Open "select * from saldetail where id = '" & store & "' and Date = '" & dtPay & "'", conn, adOpenStatic, adLockOptimisti c
If Not rs1.EOF Then
MsgBox "duplicate entry"
Else
conn.Execute "insert into saldetail(id,da te,othours,otam t,taxrate,netsa l,pro vince)values ('" & store & "','" & dtpay & "','" & txtoh & "','" & txtotamt & "','" & txttr & "','" & txtnsal & "','" & cbostate & "')"
MsgBox "INSERTED NEW RECORDS", vbOKOnly, "ADDING NEW RECORDS"
rs1.Close
Set rs1 = Nothing
End IF
[/code]

Regards
Veena

pk can be set as if in ms-access in sql server,so kept both as primary...but im not getting it,the way i meant to be...

[9815402440]

hi
i think every possible way has been suggested by the experts. if still there is any problam then you can try following logic.

[CODE=vb]rs.Open "select id, date, id & format(date,'dd/mm/yyyy') as key from table"

rs.Movefirst
rs.Find "key = '" & txtid.text & format(txtdate. text,"dd/mm/yyyy") & "'"
If Not rs.Eof Then
MsgBox "duplicate entry"
Else
'insert new values
End If[/CODE]

regards
manpreet singh dhillon hoshiarpur