Setting a Primary Key 1 : Primary Key « Key « SQL / MySQL






  1. SQL / MySQL
  2. Key
  3. Primary Key

Setting a Primary Key 1

  
/*
mysql> Drop TABLE Employee;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) NOT NULL,
    ->    Phone   VARCHAR(15) DEFAULT 'Unknown Phone' NOT NULL,
    ->    CONSTRAINT MyPrimaryKey PRIMARY KEY (Name));
Query OK, 0 rows affected (0.07 sec)

mysql> Describe Employee;
+-------+-------------+------+-----+---------------+-------+
| Field | Type        | Null | Key | Default       | Extra |
+-------+-------------+------+-----+---------------+-------+
| Name  | varchar(50) |      | PRI |               |       |
| Phone | varchar(15) |      |     | Unknown Phone |       |
+-------+-------------+------+-----+---------------+-------+
2 rows in set (0.01 sec)

mysql> INSERT INTO Employee (Name, Phone) VALUES ('Joe Wang', '666 2323');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO Employee (Name) VALUES ('John Doe');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employee (Name, Phone) VALUES ('Joe Wang', NULL);
ERROR 1048 (23000): Column 'Phone' cannot be null
mysql> Select * from Employee;
+----------+---------------+
| Name     | Phone         |
+----------+---------------+
| Joe Wang  | 666 2323      |
| John Doe | Unknown Phone |
+----------+---------------+
2 rows in set (0.00 sec)

*/

Drop TABLE Employee;
  
CREATE TABLE Employee (
   Name    VARCHAR(50) NOT NULL, 
   Phone   VARCHAR(15) DEFAULT 'Unknown Phone' NOT NULL,
   CONSTRAINT MyPrimaryKey PRIMARY KEY (Name));

Describe Employee;

INSERT INTO Employee (Name, Phone) VALUES ('Joe Wang', '666 2323');
INSERT INTO Employee (Name) VALUES ('John Doe');
INSERT INTO Employee (Name, Phone) VALUES ('Joe Wang', NULL);

Select * from Employee;








Related examples in the same category

1.Setting a Primary Key 2
2.Setting a Primary Key 3
3.Define and use primary key
4.Alter table to Add an PRIMARY KEY
5.Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.
6.To drop an index that is not a PRIMARY KEY, you must specify the index name.
7.Adding primary key for not null column
8.Defining Primary Keys
9.Use a PRIMARY KEY constraint
10.If you were creating a primary key on more than one column, you would include both of those column names in th
11.Two ways to declare primary key when creating the table
12.Create a table t that contains an id column that's NOT NULL and declared as a primary key by means of a PRIMAR
13.A primary key on a column can be created by replacing PRIMARY KEY with UNIQUE in the table definition, provide
14.Add PRIMARY KEY or UNIQUE directly to the end of the column definition.
15.Create a primary key on the last_name and first_name columns using a PRIMARY KEY clause
16.Create a multiple-column primary key using UNIQUE, if the columns are declared NOT NULL
17.Using three column as the primary key
18.Drop primary key