Mysql: Name Primary Key in Create Table Statement

PRIMARY KEY definition in MySQL CREATE TABLE statement

The second syntax is merely a shortcut allowing you to specify the column and add an index on it in a single clause.

This works out fine in cases where you simply want to create a column and add an index on it.

You'll need to use the first syntax if you want to do something more complicated, such as adding an index based on multiple columns rather than a single column, or if you are adding or changing an index on an existing column; that is, you are not creating the column and the index on it at the same time.

MySql primary key constraint with name

It's the same as MySQL ignores the CONSTRAINT pk_PersonID part. You can check by creating the table and then dumping it or issuing SHOW CREATE TABLE Persons.

I guess it supports this syntax only for compatibility with other SQL servers (but ignores it for primary and other local keys) and does not store its information (the constraint name).

However for usage with foreign keys the CONSTRAINT keyword is used also in MySQL.

mysql> CREATE TABLE test.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)
    -> );
Query OK, 0 rows affected (0.50 sec)

server$ mysqldump -p test Persons
Enter password:
--
-- Table structure for table `Persons`
--
DROP TABLE IF EXISTS `Persons`;
CREATE TABLE `Persons` (
  `P_Id` int(11) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`P_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is also test to prove MySQL doesn't store the constraint name anywhere and doesn't use it when printing errors (as mentioned for other SQL servers in question What is the purpose of constraint naming :

mysql> insert into Persons (P_Id) values(1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Persons (P_Id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

How to create a primary key in a CREATE TABLE...SELECT?

Add table definition to the query.

$sql = "CREATE TABLE survey_results_activities_temp (
                   id INT NOT NULL PRIMARY KEY, 
                   taxyear INT NOT NULL,
                   employee_email VARCHAR(255),
                   ... , 
                   risk_pharma INT )
        SELECT e.id, taxyear, employee_email, w2_wages, employee_title, department, job_code, cc.cost_center_name, e.consultant, pr.cost_center as cost_center_pharma, pr.risk as risk_pharma
        FROM employees e, cost_center cc, pharma_risk pr
        WHERE e.email='$_SESSION[userid]' AND e.campaign= '$_SESSION[campaign]' AND e.qualified=1 AND cc.id=e.department AND cc.pharma_like=pr.id";

Of course, datatypes and other definition details are fake, adjust them.

Remember - the names of columns in output list of SELECT query must match the column names in table definition strictly!

Final columns order in the table will match their order in output list in SELECT part, not columns definition order in CREATE part.

Create a table who's name must be a primary key in another table

This was originally written for SQL Server, but concept is same for every RDBMS.

Yes it is achievable, but Curse and Blessing Dynamic SQL by Erland Sommarskog

CREATE TABLE @tbl

The desire here is to create a table of which the name is determined at run-time.

If we just look at the arguments against using dynamic SQL in stored procedures, few of them are really applicable here. If a stored procedure has a static CREATE TABLE in it, the user who runs the procedure must have permissions to create tables, so dynamic SQL will not change anything. Plan caching obviously has nothing to do with it. Etc.

Nevertheless: Why? Why would you want to do this? If you are creating tables on the fly in your application, you have missed some fundamentals about database design. In a relational database, the set of tables and columns are supposed to be constant. They may change with the installation of new versions, but not during run-time.

EDIT:

In SQL Server I would use trigger for source table (FOR INSERT/UPDATE/DELETE) and using Dynamic-SQL I can achieve exactly what you want, but still I think this is madness.

If you want to go this path check if MySQL supports the same.

Can we use constraint name of a primary key as foreign key reference?

I don't think so because there's no way that the RDBMS could know whether PK_name is a column or a constraint name so I suggest if you stick with the usual :

create table A (
department_id int,
college_id int,
constraint Pk_name primary key(department_id,college_id)
);

create table B (
student_name varchar(75),
department_id int,
college_id int,
foreign key(department_id,college_id) references A(department_id,college_id)
);

I will update the answer once I find an other answer .

How to make SQL Primary key have a specific number of characters?

Primary keys need to have one job only, that of uniquely identifying a row. As soon as you start trying to make them look presentable by formatting them or make them sequential without gaps, or even when you try to use them to see if one row was created before another, you create reasons to want to change them.

Practically anything visible to users or involved in business logic is going to end up needing to change. And primary keys shouldn't change. Changing a primary key means deleting the row and making a new one with the new key value, and also fixing all the references to the old key. It's fiddly and tedious and error-prone, it is something you want to avoid.

Make a separate column for a user-visible identifier separate from the PK that you can have full control over. You can populate it with a trigger or application code based off the key if you want. Just keep it separate from the primary key.

---

Related Topics