c# MySQL Auto Increment

Question

In Table A I have three fields. I want ID to be auto incremented without setting it as the primary key, But I want to make Color SN and Color PN the primary Key.
But the database will not allow me. The error says that AutoIncrement has to be an primary key. can someone assist me. My goal is to not have a duplicate Color SN or Color PN

Table A

ID

Color SN

Color PN

Vulpes · Answer

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.

David Smith · Answer

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

David Smith · Answer

I am working from visual studio designer, create table command is not supported

David Smith · Answer

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?

Vulpes · Answer

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.

Javeed M Shaikh · Answer

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.

David Smith · Answer

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

David Smith · Answer

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

Vulpes · Answer

Just set the PrimaryKey attribute for each of the three fields separately. The designer should then realize that a composite primary key is required.

David Smith · Answer

How to create the composite key in the designer itself in visual studio , not through query or a procedure.

Vulpes · Answer

The syntax for creating a composite key is as Javeed stated in his first post. There's another example here: http://www.java2s.com/Code/SQL/Key/Usingthreecolumnastheprimarykey.htm

David Smith · Answer

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

Javeed M Shaikh · Answer

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)

David Smith · Answer

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.

Vulpes · Answer

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.

David Smith · Answer

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

David Smith · Answer

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,

Dipal Choksi · Answer

How about setting Color SN + Color PN as a unique key? http://www.mysqlfaqs.net/mysql-faqs/Indexes/Unique-Key-or-Index/How-to-create-multi-column-unique-key-or-index-in-MySQL

Javeed M Shaikh · Answer

how about primary key(id,ColorSN,ColorPN) ?