-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatabase_table.sql
More file actions
executable file
·30 lines (25 loc) · 983 Bytes
/
Copy pathdatabase_table.sql
File metadata and controls
executable file
·30 lines (25 loc) · 983 Bytes
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- CREATE DATABASE project3;
--
-- USE project3;
CREATE TABLE users(username nvarchar(255) NOT NULL
, displayname nvarchar(MAX) NOT NULL
, picture nvarchar(MAX)
, PRIMARY KEY CLUSTERED (username)
);
-- This is server user, insert this to users first else get conflict constraint
-- when insert message to MessageDetail table, Cause client send a message to server
-- then server save it to MessageDetail and there is no user with this from user
-- existed in users table, so conflict will be occcured
INSERT INTO users(username, displayname) values('Server', 'Server');
CREATE TABLE MessageDetail(MessageID int IDENTITY(1,1) NOT NULL
, FromUser NVARCHAR(255)
, ToUser NVARCHAR(255)
, DateCreated smalldatetime NOT NULL
, [Content] NVARCHAR(MAX)
, MessageType NVARCHAR(255) NOT NULL
, PRIMARY KEY CLUSTERED (MessageID)
, FOREIGN KEY (FromUser) REFERENCES users(username)
, FOREIGN KEY (ToUser) REFERENCES users(username)
);
-- drop table MessageDetail;
-- drop table users;