SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
SQL PRIMARY KEY on CREATE TABLE
SQL PRIMARY KEY Constraint. The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
The following SQL creates a PRIMARY KEY on the 'ID' column when the 'Persons' table is created:
MySQL:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
SQL PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the 'ID' column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
Generate Key Windows
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
MySQL:
SQL Server / Oracle / MS Access:
Posted by: Vikram Chhetry
Date: April 26, 2011 02:22AM
Date: April 26, 2011 02:22AM
Hi All,
I am creating one web application with mysql innodb tables. Now, the scenario is that my application & database runs on a public server and same application & database runs on individual local machines(multiple) where there is no interet connection. The only protocol that is enabled in local machines is SMTP. The requirement of my project is to bring all the databases in sync everyday via. email which will have an attachment as an xml with updated/inserted rows. Problem we faced was to generate primary keys which are universally unique i.e. if there is any insert in any local machine and on server, that id should never get generated in any other local machine. To resolve this problem we made all the primary keys as varchar (bad idea) and to generate unique value what we do is as below:
1.) Local machines: Count all the rows in the table and add 1 to it and concatenate a unique value allocated to a local machine. So if any row is inserted in the local machines the value would look like localmachineid_count+1. Ex: 1_1 (local machine id = 1), 2_1 (local machine id = 2) for same table.
2.) Public server: Count all the rows in the table and add 1 to it.
Now i understand that this thing will fail in two cases, when there is simultaneous insert request for same table and when some one deletes any row.
Is there any way of handling this scenario in mysql (like: sequence).
Any help would be appreciated.
Thanks in advance.
--
Vikram
I am creating one web application with mysql innodb tables. Now, the scenario is that my application & database runs on a public server and same application & database runs on individual local machines(multiple) where there is no interet connection. The only protocol that is enabled in local machines is SMTP. The requirement of my project is to bring all the databases in sync everyday via. email which will have an attachment as an xml with updated/inserted rows. Problem we faced was to generate primary keys which are universally unique i.e. if there is any insert in any local machine and on server, that id should never get generated in any other local machine. To resolve this problem we made all the primary keys as varchar (bad idea) and to generate unique value what we do is as below:
1.) Local machines: Count all the rows in the table and add 1 to it and concatenate a unique value allocated to a local machine. So if any row is inserted in the local machines the value would look like localmachineid_count+1. Ex: 1_1 (local machine id = 1), 2_1 (local machine id = 2) for same table.
2.) Public server: Count all the rows in the table and add 1 to it.
Now i understand that this thing will fail in two cases, when there is simultaneous insert request for same table and when some one deletes any row.
Is there any way of handling this scenario in mysql (like: sequence).
Any help would be appreciated.
Thanks in advance.
--
Vikram
Options:Reply•Quote
Foreign Key
Written By
Generate Primary Key Without Table Of Money
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.