CODEWITHSUNDEEP
mysql
DinaLizooo
DinaLizooo

Reputation: 13

making two columns primary key

Hello i have a table which has one primary key by the name of ImageID and i want to make another column

also primary key which is PropertyID means Composite Key

HERE IS THE CODE, but its showing this error for me "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( PropertyID INT, ImageID INT primary key (PropertyID, ImageID) )' at line "

Also the ImageID is already primary key, but with varchar(15) specification. 

Alter TABLE properties (
  PropertyID INT,
  ImageID INT,
  primary key (PropertyID, ImageID)
);

Upvotes: 0

Views: 962

Answers (3)

Ashot Karakhanyan
Ashot Karakhanyan

Reputation: 2830

As I understand you already have a table with one key, which looks like the following:

CREATE TABLE `common`.`properties` (
  `PropertyID` VARCHAR(15) NOT NULL,
  `otherColumn` VARCHAR(45) NULL,
  PRIMARY KEY (`PropertyID`));

And now you want to add another PK column and change the type of existing PK column from char to INT. So you need to do it as following:

ALTER TABLE `common`.`properties` 
CHANGE COLUMN `PropertyID` `PropertyID` INT NOT NULL ,
ADD COLUMN `ImageID` INT NOT NULL AFTER `otherColumn`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`PropertyID`, `ImageID`);

P.S. common is my schema name, you can use your own, or even skip it if your schema is default.

Upvotes: 0

Ansh
Ansh

Reputation: 94

Try this:

First drop the existing primary key

ALTER TABLE properties
DROP PRIMARY KEY;

Then add composite key

ALTER TABLE properties
ADD PRIMARY KEY(imageID, propertyID);

Upvotes: 0

KriiV
KriiV

Reputation: 2020

Each table can only have 1 primary key. You can only have one primary key, but you can have multiple columns in your primary key.

Taken from W3Schools:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Here the only primary key is pk_PersonID but that have stated that pk_PersonID is made up of P_Id and LastName.

Unique Indexes may be what you're looking for. This means unique values are required and runs like an index in that it can speed up queries.

Upvotes: 1

Related Questions