1create table user(
2 user_id INT auto_increment PRIMARY KEY,
3 username varchar(50) not null,
4 avator varchar(50),
5 gender boolean not null,
6 phone varchar(20),
7 unique(username)
8);
9
10create table journey(
11 id INT auto_increment PRIMARY KEY,
12 start varchar(100) not null,
13 dest varchar(100) not null,
14 time date not null,
15 person INT,
16 user_id INT not null,
17 foreign key(user_id) references user(user_id)
18);
19
1USE AdventureWorks2012;
2GO
3CREATE TABLE Production.TransactionHistoryArchive4
4 (
5 TransactionID int NOT NULL,
6 CONSTRAINT AK_TransactionID UNIQUE(TransactionID)
7);
8GO
9
10
1DISTINCT
2- select distinct * from employees; ==> retrieves any row if it has at
3least a single unique column.
4- select distinct first_name from employees; ==> retrieves unique names
5from table. (removes duplicates)
6- select distinct count(*) from employees; retrieve number of unique rows
7if any row has at least a single unique data.
1CREATE TABLE Students ( /* Create table with a single field as unique */
2 ID INT NOT NULL UNIQUE
3 Name VARCHAR(255)
4);
5
6CREATE TABLE Students ( /* Create table with multiple fields as unique */
7 ID INT NOT NULL
8 LastName VARCHAR(255)
9 FirstName VARCHAR(255) NOT NULL
10 CONSTRAINT PK_Student
11 UNIQUE (ID, FirstName)
12);
13
14ALTER TABLE Students /* Set a column as unique */
15ADD UNIQUE (ID);
16
17ALTER TABLE Students /* Set multiple columns as unique */
18ADD CONSTRAINT PK_Student /* Naming a unique constraint */
19UNIQUE (ID, FirstName);
20