56 lines
1.4 KiB
SQL
56 lines
1.4 KiB
SQL
USE friendbook;
|
|
|
|
CREATE TABLE login(
|
|
username varchar(25) NOT NULL,
|
|
pword varchar(25) NOT NULL,
|
|
primary key(username));
|
|
|
|
CREATE TABLE contacts(
|
|
username varchar(25) NOT NULL,
|
|
fname varchar(25) NOT NULL,
|
|
lname varchar(25) NOT NULL,
|
|
primary key(username),
|
|
foreign key(username) references login(username));
|
|
|
|
CREATE TABLE messages(
|
|
messageID int NOT NULL AUTO_INCREMENT,
|
|
sender varchar(25) NOT NULL,
|
|
recipient varchar(25) NOT NULL,
|
|
message text NOT NULL,
|
|
date date NOT NULL,
|
|
haveread varchar(1),
|
|
primary key(messageID),
|
|
foreign key(sender) references contacts(username),
|
|
foreign key(recipient) references contacts(username));
|
|
|
|
CREATE TABLE friendList(
|
|
username varchar(25) NOT NULL,
|
|
friend varchar(25) NOT NULL,
|
|
confirm boolean);
|
|
|
|
INSERT INTO login
|
|
(username, pword)
|
|
VALUES
|
|
('user1', 'password1'),
|
|
('user2', 'password2'),
|
|
('user3', 'password3');
|
|
|
|
INSERT INTO contacts
|
|
(username, fname, lname)
|
|
VALUES
|
|
('user1', 'Larry', 'Dupree'),
|
|
('user2', 'John', 'Smith'),
|
|
('user3', 'Jane', 'Doe');
|
|
|
|
INSERT INTO messages
|
|
(sender, recipient, message, date, haveread)
|
|
VALUES
|
|
('user1', 'user2', 'hello, how are you', now(), 'Y'),
|
|
('user2', 'user1', 'im doing good, thanks', now(), 'N');
|
|
|
|
INSERT INTO friendList
|
|
(username, friend, confirm)
|
|
VALUES
|
|
('user2', 'user1', false),
|
|
('user1', 'user3', true),
|
|
('user3', 'user1', true); |