6> CREATE TABLE authors(
7> au_id varchar(11),
8> au_lname varchar(40) NOT NULL,
9> au_fname varchar(20) NOT NULL,
10> phone char(12) NOT NULL DEFAULT ('UNKNOWN'),
11> address varchar(40) NULL,
12> city varchar(20) NULL,
13> state char(2) NULL,
14> zip char(5) NULL,
15> contract bit NOT NULL
16> )
17> GO
1> insert authors values('1', 'Joe', 'Abra', '111 111-1111', '6 St.', 'Berkeley', 'CA', '11111', 1)
2> insert authors values('2', 'Jack', 'Majo', '222 222-2222', '3 St.', 'Oakland' , 'CA', '22222', 1)
3> insert authors values('3', 'Pink', 'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4', 'Blue', 'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5', 'Red', 'Anne', '555 555-5555', '6 Av.', 'Regina', 'SK', '55555', 1)
6> insert authors values('6', 'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina', 'SK', '66666', 1)
7> insert authors values('7', 'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8', 'Yellow','Heather','888 888-8888', '3 Pu', 'Vacaville', 'CA', '88888', 0)
9> insert authors values('9', 'Gold', 'Dep', '999 999-9999', '5 Av.', 'Oakland', 'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean', '000 000-0000', '4 Av.', 'Oakland', 'CA', '00000', 1)
11> GO
1>
2> CREATE TABLE jobs(
3> job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
4> job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet',
5> min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),
6> max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)
7> )
8> GO
1>
2>
3> insert jobs values ('Coder', 10, 10)
4> insert jobs values ('Tester', 200, 250)
5> insert jobs values ('Programmer', 175, 225)
6> insert jobs values ('Painter', 175, 250)
7> insert jobs values ('Drawer', 150, 250)
8> insert jobs values ('Editor', 140, 225)
9> insert jobs values ('Manager', 120, 200)
10> insert jobs values ('Manager', 100, 175)
11> insert jobs values ('Representative', 25, 100)
12> insert jobs values ('Designer', 25, 100)
13>
14> GO
1>
2>
3> BEGIN TRAN
4> UPDATE authors
5> SET state = 'FL'
6> WHERE state = 'KS'
7>
8> IF @@ERROR <> 0 BEGIN
9> ROLLBACK TRAN
10> GOTO ON_ERROR
11> END
12> UPDATE jobs
13> SET min_lvl = min_lvl - 10
14> IF @@ERROR <> 0 BEGIN
15> ROLLBACK TRAN
16> GOTO ON_ERROR
17> END
18>
19> COMMIT TRAN
20> ON_ERROR:
21>
22> SELECT * FROM authors
23> WHERE state = 'FL'
24> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 12
The UPDATE statement conflicted with the CHECK constraint "CK__jobs__min_lvl__5B0F49A1". The conflict occurred in database "master", table "dbo.jobs", column 'min_lvl'.
The statement has been terminated.
au_id au_lname au_fname phone address city state zip contract
----------- ---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------
1>
2> drop table authors;
3> drop table jobs;
4> GO