uBsha7mt

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.
62-- SELECT SCOPE_IDENTITY()
63-- GO
64
65
66-- IDENT_CURRENT(name) returns the last identity created for a specific table or view in any session.
67SELECT IDENT_CURRENT('dbo.PlayerInfo')
68GO
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 ROWGUIDCOL 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
93    NO MINVALUE
94    NO MAXVALUE 
95GO
96
97INSERT INTO dbo.PlayerInfoSEQ VALUES(NEXT VALUE FOR dbo.IncrementID, 'x1Dman')
98INSERT INTO dbo.PlayerInfoSEQ VALUES(NEXT VALUE FOR dbo.IncrementID, 'Pandora')
99GO
100
101DROP TABLE dbo.PlayerInfoSEQ
102DROP SEQUENCE dbo.IncrementID
103GO
104
105DROP TABLE IF EXISTS dbo.CTF_PLAYER
106GO
107
108DROP TABLE IF EXISTS dbo.CTF_TEAM
109GO
110
111CREATE TABLE dbo.CTF_TEAM
112(
113    teamID int IDENTITY PRIMARY KEY ,
114    tableScore int ,
115    teamName varchar(15) NOT NULL
116)
117GO
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 : CASCADE will propagate the change when the parent changes.
169If you delete a row, rows in constrained tables that reference that row will also be deleted
170каскадное изменение ссылающихся таблиц;
171*/
172
173ALTER TABLE dbo.CTF_PLAYER DROP CONSTRAINT FK_TEAM_ID
174
175ALTER TABLE dbo.CTF_PLAYER
176ADD CONSTRAINT FK_TEAM_ID FOREIGN KEY (teamIDFK)
177    REFERENCES dbo.CTF_TEAM (teamID)
178    ON DELETE CASCADE
179    ON UPDATE CASCADE
180
181UPDATE dbo.CTF_TEAM
182SET teamName = 'LOOSERS'
183DELETE FROM dbo.CTF_TEAM
184GO
185
186
187
188-- *SET DEFAULT*
189-- установка значений по умолчанию для ссылающихся внешних ключей;
190
191ALTER TABLE dbo.CTF_PLAYER DROP CONSTRAINT FK_TEAM_ID
192
193ALTER TABLE dbo.CTF_PLAYER
194ADD CONSTRAINT FK_TEAM_ID FOREIGN KEY (teamIDFK)
195    REFERENCES dbo.CTF_TEAM (teamID)
196    ON DELETE SET DEFAULT
197    ON UPDATE SET DEFAULT
198GO
199
200UPDATE dbo.CTF_TEAM
201SET teamName = 'LOOSERS'
202DELETE FROM dbo.CTF_TEAM
203GO
204
205
206-- *SET NULL*
207/*Set NULL : Sets the column value to NULL when you delete the parent table row.*/
208
209ALTER TABLE dbo.CTF_PLAYER DROP CONSTRAINT FK_TEAM_ID
210
211ALTER TABLE dbo.CTF_PLAYER
212ADD CONSTRAINT FK_TEAM_ID FOREIGN KEY (teamIDFK)
213    REFERENCES dbo.CTF_TEAM (teamID)
214    ON DELETE SET NULL
215    ON UPDATE SET NULL
216GO
217
218UPDATE dbo.CTF_TEAM
219SET teamName = 'LOSERS'
220DELETE FROM dbo.CTF_TEAM
221GO