c# MySQL Auto Increment

David Smith

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

19 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
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 Smith15y ago
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 Smith15y ago
I am working from visual studio designer, create table command is not supported
David Smith15y ago
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?
Vulpes15y ago
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 Shaikh15y ago
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 Smith15y ago
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 Smith15y ago
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
Vulpes15y ago
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 Smith15y ago
How to create the composite key in the designer itself in visual studio , not through query or a procedure.
Vulpes15y ago
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 Smith15y ago
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 Shaikh15y ago
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 Smith15y ago
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.
Vulpes15y ago
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 Smith15y ago
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 Smith15y ago
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 Choksi15y ago
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 Shaikh15y ago
how about primary key(id,ColorSN,ColorPN) ?