asked on

mysql Composite primary key & Auto increament

hi ,
I am moving my application from mssql to my sql .
And i have table in mssql which has composite primary key and auto increment field both .
user_contract_ID is auto incremant while userid and contract_id is primary key .
I want to create same table in mysql . Is it possible ?
Thanks

CREATE TABLE [User_contract] (
	[User_contract_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[User_ID] [bigint] NOT NULL ,
	[contract_ID] [bigint] NOT NULL ,
	[Contract_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_User_contract] PRIMARY KEY  CLUSTERED 
	(
		[User_ID],
		[contract_ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Open in new window

ASKER CERTIFIED SOLUTION

oobayly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Terry Woods

From http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html:

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
);

Terry Woods

ie It's allowed, but probably won't meet your requirements as the userid by itself won't be unique.

racek

You can first create 2 tables: Contracts and Users both with auto_increment.
Then - when you are inserting a row to your table - CONTRACT, use a trigger for create missing id's or fetching already existing

mysql Composite primary key &amp; Auto increament

mysql Composite primary key & Auto increament