0
If you do that, then it will rule out ID being an auto-increment field.
However, may be you could figure out a way to increment the last value 'manually' when you create a new record.
0 o vuples the best thing to is remove the ID and just use the color sn and color pn as the primary key, it works well that way, if i do that i got to re organized the dataase, Before i do that i am going to do some more researching
0 I am working from visual studio designer, create table command is not supported
0 so basically the only way to do it is to use the create table procedure you created, its no other way to do it through the designer?
0 I think Darnell's point is that having a composite key including ID won't now prevent the possibility of Color SN and Color PN being duplicated in two or more records.
If the primary key had consisted of just Color SN and Color PN, then this wouldn't have been possible.
0 Darnell,
you are right, this is a composite key so combination of all three is unique in this case. I tried to do the same thing in SQL Server and it works fine, following is the DDL:
CREATE TABLE [dbo].[mytable](
[id] [int] IDENTITY(1,1) NOT NULL,
[colorsn] [varchar](50) NOT NULL,
[colorpn] [varchar](50) NOT NULL,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED
(
[colorsn] ASC,
[colorpn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
as you can see here keys are colorsn and colorpn and id is just an identity column and not primary key.
0 What allows the duplicate data is the id in the table below
id color sn color pn
1 sn A pn A
2 sn A pn A
0 I tried that, Highlight all three columns, went to tools and click set as a primary key and it allow this behavior below, which is not what i want
id color sn color pn
1 sn A pn A
2 sn A pn A
0 Just set the PrimaryKey attribute for each of the three fields separately.
The designer should then realize that a composite primary key is required.
0 How to create the composite key in the designer itself in visual studio , not through query or a procedure.
0 0 How do you make a composite key.
Are you saying with the composite key. Can you give me an example of how to make a primary key.
This is the situation I do not want.
id color sn color pn
1 sn A pn A
2 sn A pn A
0 that said you will have unique value with the combination of ID,ColorSN,ColorPN when you make composite key (think of it as primary key on more then one column)
0 What do you mean when you say a composite key?
So are you saying there is no way to an auto incremented id thats noot a primary key.
Is i were to make ID, Color Sn, ans Color Pn a primary I can have duplicate data.
0 I don't think there is any other way to do it apart from making a composite primary key out of ID, Color SN and Color PN, as Javeed suggested.
0 I am edit from the SQL server view in c #, keep that in ind, i am using the table designer to do my editing for tables
0 I tried setting the Color SN, and Color ON as a unique key, and remove the Pk from the auto incremented ID, but when i click save it says that an auto incremented ID must be set as PK,
0 0 how about primary key(id,ColorSN,ColorPN) ?