(1000, N ' ..','19550219',2,1,NULL),
(1001, N ' ..','19831203',3,3,1003),
(1002, N ' ..','19760607',1,2,1000),
(1003, N ' ..','19820417',4,3,1000)
DDL:
IDENTITY (-) ;
ALTER TABLE _ ADD ___ ;
ALTER TABLE _ DROP COLUMN _ ;
ALTER TABLE _ ADD CONSTRAINT _ FOREIGN KEY () REFERENCES _() .
UNIQUE, DEFAULT, CHECK
UNIQUE . Employees, Email. Email , :
UPDATE Employees SET Email='[email protected]' WHERE ID =1000
UPDATE Employees SET Email='[email protected]' WHERE ID =1001
UPDATE Employees SET Email='[email protected]' WHERE ID =1002
UPDATE Employees SET Email='[email protected]' WHERE ID =1003
-:
ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE (Email)
E-Mail .
UQ_, , .
, :
ALTER TABLE _ ADD CONSTRAINT _ UNIQUE (1,2,)
DEFAULT , , INSERT. .
Employees HireDate :
ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()
HireDate , :
ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate
, .. DEFAULT , . -, , , . :
ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
|
|
, HireDate .
, , , .. . , HireDate :
INSERT Employees(ID, Name,Email) VALUES (1004, N ' ..','[email protected]')
, :
SELECT * FROM Employees
ID | Name | Birthday | PositionID | DepartmentID | ManagerID | HireDate | |
.. | 1955-02-19 | [email protected] | NULL | 2015-04-08 | |||
.. | 1983-12-03 | [email protected] | 2015-04-08 | ||||
.. | 1976-06-07 | [email protected] | 2015-04-08 | ||||
.. | 1982-04-17 | [email protected] | 2015-04-08 | ||||
.. | NULL | [email protected] | NULL | NULL | NULL | 2015-04-08 |
CHECK , . , , (ID). , 1000 1999:
ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999)
, CK_, , .
, ( ):
INSERT Employees(ID,Email) VALUES (2000,'[email protected]')
1500 , :
INSERT Employees(ID,Email) VALUES (1500,'[email protected]')
UNIQUE CHECK :
ALTER TABLE Employees ADD UNIQUE (Email)
ALTER TABLE Employees ADD CHECK (ID BETWEEN 1000 AND 1999)
, .. , , , .
.
, , , . :
DROP TABLE Employees
CREATE TABLE:
CREATE TABLE Employees(
ID int NOT NULL,
Name nvarchar (30),
Birthday date,
Email nvarchar (30),
PositionID int,
DepartmentID int,
HireDate date NOT NULL DEFAULT SYSDATETIME(), -- DEFAULT
CONSTRAINT PK_Employees PRIMARY KEY (ID),
CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY (DepartmentID) REFERENCES Departments(ID),
CONSTRAINT FK_Employees_PositionID FOREIGN KEY (PositionID) REFERENCES Positions(ID),
CONSTRAINT UQ_Employees_Email UNIQUE (Email),
CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999)
)
: