-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathforeignKey.sql
More file actions
63 lines (38 loc) Β· 1.27 KB
/
foreignKey.sql
File metadata and controls
63 lines (38 loc) Β· 1.27 KB
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- Active: 1699467625194@@127.0.0.1@5432@ph@public
CREATE Table "user"(
id SERIAL PRIMARY KEY,
username VARCHAR(25) NOT NULL
)
CREATE Table post(
id SERIAL PRIMARY KEY,
title text NOT NULL,
user_id INTEGER REFERENCES "user"(id) on delete set DEFAULT DEFAULT 2
)
ALTER Table post
alter COLUMN user_id set NOT null;
INSERT INTO "user" (username) VALUES
('akash'),
('batash'),
('sagor'),
('nodi');
INSERT INTO post (title, user_id) VALUES
('Enjoying a sunny day with Akash! βοΈ', 2),
('Batash just shared an amazing recipe! π²', 1),
('Exploring adventures with Sagor.π', 4),
('Nodi''s wisdom always leaves me inspired. π', 4);
DROP Table post;
DROP Table "user";
SELECT * from "user";
SELECT * from post;
INSERT INTO post (title, user_id) VALUES('test', NULL)
-- Insertion constraint on INSERT post
-- Attempting to insert a post with a user ID that does not exist
-- Inserting a post with a valid user ID
-- Attempting to insert a post without specifying a user ID
DELETE FROM "user"
WHERE id = 4;
-- Deletion constraint on DELETE user
-- Restrict Deletion -> ON DELETE RESTRICT / ON DELETE NO ACTION (default)
-- Cascading Deletion -> ON DELETE CASCADE
-- Setting NULL -> ON DELETE SET NULL
-- Set Default value -> ON DELETE SET DEFAULT