PNtEpu5N

1USE master
2GO
3
4DROP DATABASE IF EXISTS LAB6;
5
6CREATE DATABASE LAB6
7    ON  (
8        NAME = LAB6 ,
9        FILENAME = '/Users/Apple/Desktop/DATABASE/lab6/lab6.mdf' ,
10        SIZE  = 5MB ,
11        MAXSIZE = UNLIMITED ,
12        FILEGROWTH = 5MB
13    )
14    LOG ON (
15        NAME = LAB6LOG ,
16        FILENAME = '/Users/Apple/Desktop/DATABASE/lab6/lab6_log.ldf' ,
17        SIZE = 1MB ,
18        MAXSIZE = 10MB ,
19        FILEGROWTH = 1MB
20    )
21GO
22
23USE LAB6
24GO
25
26CREATE FUNCTION dbo.CreateTeamTag
27(@team varchar(10), @name varchar(10))
28RETURNS char(21)
29    BEGIN
30        DECLARE @teamTag varchar(21)
31        SELECT @teamTag = @team + '.' + @name
32        RETURN @teamTag
33    END;
34GO
35
36CREATE TABLE dbo.PlayerInfo
37(
38        playerID int PRIMARY KEY IDENTITY(1, 1) NOT NULL ,
39        nickName varchar(10) NOT NULL ,
40        age int CHECK (age > 15) NOT NULL ,
41        team varchar(10) NOT NULL ,
42        presentedCountry varchar(15) DEFAULT NULL ,
43        teamTag AS dbo.CreateTeamTag(team, nickName)
44)
45GO
46
47
48INSERT INTO dbo.PlayerInfo(nickName, age, team, presentedCountry)
49    VALUES ('x1Dman', 17, 'SFT0', 'Russia')
50GO
51
52SELECT * FROM dbo.PlayerInfo
53GO
54
55
56-- @@IDENTITY function returns the last identity created in the same session.
57-- SELECT @@IDENTITY FROM dbo.PlayerInfo
58-- GO
59
60
61--SCOPE_IDENTITY() function returns the last identity created in the same session and the same scope.
62SELECT SCOPE_IDENTITY()
63GO
64
65
66-- IDENT_CURRENT(name) returns the last identity created for a specific table or view in any session.
67-- SELECT IDENT_CURRENT('dbo.PlayerInfo')
68--  GO
69
70DROP TABLE IF EXISTS dbo.PlayerInfo
71GO
72
73DROP TABLE IF EXISTS dbo.ID_UNIQ_SEQ
74GO
75
76CREATE TABLE dbo.ID_UNIQ_SEQ
77(ID UNIQUEIDENTIFIER PRIMARY KEY NOT NULL DEFAULT NEWID())
78GO
79
80INSERT INTO dbo.ID_UNIQ_SEQ VALUES (NEWID())
81GO
82
83CREATE TABLE dbo.PlayerInfoSEQ
84(
85    playerID int PRIMARY KEY,
86    nickName varchar(15) NOT NULL
87)
88GO
89
90CREATE SEQUENCE dbo.IncrementID AS int
91    START WITH 0
92    INCREMENT BY 1
93GO
94
95INSERT INTO dbo.PlayerInfoSEQ VALUES(NEXT VALUE FOR dbo.IncrementID, 'x1Dman')
96INSERT INTO dbo.PlayerInfoSEQ VALUES(NEXT VALUE FOR dbo.IncrementID, 'Pandora')
97GO
98
99DROP TABLE dbo.PlayerInfoSEQ
100DROP SEQUENCE dbo.IncrementID
101GO
102
103DROP TABLE IF EXISTS dbo.CTF_PLAYER
104GO
105
106DROP TABLE IF EXISTS dbo.CTF_TEAM
107GO
108
109
110CREATE TABLE dbo.CTF_TEAM
111(
112    teamID int IDENTITY PRIMARY KEY ,
113    tableScore int ,
114    teamName varchar(15) NOT NULL
115)
116GO
117
118
119INSERT INTO dbo.CTF_TEAM(tableScore, teamName)
120VALUES (4, 'SFT0') ,
121       (6, 'SFT0KIDS') ,
122       (4, 'bushwackers')
123GO
124
125
126CREATE TABLE dbo.CTF_PLAYER
127(
128    playerID int IDENTITY PRIMARY KEY ,
129    nickName varchar(15) NOT NULL ,
130    teamIDFK int ,
131    CONSTRAINT FK_TEAM_ID FOREIGN KEY (teamIDFK)
132        REFERENCES dbo.CTF_TEAM (teamID)
133)
134GO
135
136INSERT INTO dbo.CTF_PLAYER(nickName, teamIDFK)
137VALUES ('kekson322', 1),
138       ('roflret228', 1),
139       ('GENIYDOTI', 2),
140       ('EsliTiNPonimaew', 2)
141GO
142
143SELECT * FROM CTF_TEAM
144SELECT * FROM CTF_PLAYER
145GO
146
147
148-- *NO ACTION*
149/* NO ACTION (указывает, что
150при попытке удалить или изменить строку с ключом, на которую
151ссылаются внешние ключи в строках других таблиц, нужно сообщить
152об ошибке, а для инструкции DELETE/UPDATE выполнить откат);*/
153
154-- INSERT INTO CTF_TEAM VALUES (4, 'SFT0')
155--
156--
157-- INSERT INTO CTF_PLAYER VALUES ('x1Dman', 1)
158--
159-- UPDATE dbo.CTF_TEAM
160-- SET teamName = 'CTF0'
161-- DELETE FROM dbo.CTF_TEAM
162--
163-- GO
164
165
166-- *CASCADE*
167/*
168CASCADE :
169каскадное изменение ссылающихся таблиц;
170*/
171
172ALTER TABLE dbo.CTF_PLAYER DROP CONSTRAINT FK_TEAM_ID
173
174ALTER TABLE dbo.CTF_PLAYER
175ADD CONSTRAINT FK_TEAM_ID FOREIGN KEY (teamIDFK)
176    REFERENCES dbo.CTF_TEAM (teamID)
177    ON DELETE CASCADE
178    ON UPDATE CASCADE
179
180UPDATE dbo.CTF_TEAM
181SET teamName = 'LOOSERS'
182DELETE FROM dbo.CTF_TEAM
183GO
184
185
186
187-- *SET DEFAULT*
188-- установка значений по умолчанию для ссылающихся внешних ключей;
189
190ALTER TABLE dbo.CTF_PLAYER DROP CONSTRAINT FK_TEAM_ID
191
192ALTER TABLE dbo.CTF_PLAYER
193ADD CONSTRAINT FK_TEAM_ID FOREIGN KEY (teamIDFK)
194    REFERENCES dbo.CTF_TEAM (teamID)
195    ON DELETE SET DEFAULT
196    ON UPDATE SET DEFAULT
197GO
198
199UPDATE dbo.CTF_TEAM
200SET teamName = 'LOSERS'
201DELETE FROM dbo.CTF_TEAM
202GO
203
204
205-- *SET NULL*
206/*Set NULL : Sets the column value to NULL when you delete the parent table row.*/
207
208ALTER TABLE dbo.CTF_PLAYER DROP CONSTRAINT FK_TEAM_ID
209
210ALTER TABLE dbo.CTF_PLAYER
211ADD CONSTRAINT FK_TEAM_ID FOREIGN KEY (teamIDFK)
212    REFERENCES dbo.CTF_TEAM (teamID)
213    ON DELETE SET NULL
214    ON UPDATE SET NULL
215GO
216
217UPDATE dbo.CTF_TEAM
218SET teamName = 'LOSERS'
219DELETE FROM dbo.CTF_TEAM
220GO