в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:20:35Z

1 версия

Новая страница

==Default Constraints==



<source lang="sql">

4> CREATE TABLE employee
5> (
6> emp_id int NOT NULL PRIMARY KEY DEFAULT 1000
7> CHECK (emp_id BETWEEN 0 AND 1000),
8>
9> emp_name varchar(30) NULL DEFAULT NULL CONSTRAINT no_nums
10> CHECK (emp_name NOT LIKE "%[0-9]%"),
11>
12> mgr_id int NOT NULL DEFAULT (1) REFERENCES
13> employee(emp_id),
14>
15> entered_date datetime NOT NULL CHECK (entered_date >=
16> CONVERT(char(10), CURRENT_TIMESTAMP, 102))
17> CONSTRAINT def_today DEFAULT
18> (CONVERT(char(10), GETDATE(), 102)),
19>
20> entered_by int NOT NULL DEFAULT SUSER_ID()
21> CHECK (entered_by IS NOT NULL),
22>
23> CONSTRAINT valid_entered_by CHECK (entered_by=SUSER_ID() AND
24> entered_by <> emp_id),
25>
26> CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1),
27>
28> CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28)
29> )
30> GO
1>
2>
3> EXEC sp_helpconstraint employee
4> GO
Object Name

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
employee

constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys

-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
CHECK on column emp_id CK__employee__emp_id__66EB10A1
(n/a) (n/a) Enabled Is_For_Replication ([emp_id]>=(0) AND [emp_id]<=(1000))

CHECK on column entered_date CK__employee__entere__6BAFC5BE
(n/a) (n/a) Enabled Is_For_Replication ([entered_date]>=CONVERT([char](10),getdate(),(102)))

CHECK on column entered_by CK__employee__entere__6E8C3269
(n/a) (n/a) Enabled Is_For_Replication ([entered_by] IS NOT NULL)

DEFAULT on column entered_date def_today
(n/a) (n/a) (n/a) (n/a) (CONVERT([char](10),getdate(),(102)))

DEFAULT on column emp_id DF__employee__emp_id__65F6EC68
(n/a) (n/a) (n/a) (n/a) ((1000))

DEFAULT on column emp_name DF__employee__emp_na__67DF34DA
(n/a) (n/a) (n/a) (n/a) (NULL)

DEFAULT on column entered_by DF__employee__entere__6D980E30
(n/a) (n/a) (n/a) (n/a) (suser_id())

DEFAULT on column mgr_id DF__employee__mgr_id__69C77D4C
(n/a) (n/a) (n/a) (n/a) ((1))

CHECK Table Level end_of_month
(n/a) (n/a) Enabled Is_For_Replication (datepart(day,getdate())<(28))

FOREIGN KEY FK__employee__mgr_id__6ABBA185
No Action No Action Enabled Is_For_Replication mgr_id

REFERENCES master.dbo.employee (emp_id)

CHECK on column emp_name no_nums
(n/a) (n/a) Enabled Is_For_Replication (NOT [emp_name] like "%[0-9]%")

PRIMARY KEY (clustered) PK__employee__6502C82F
(n/a) (n/a) (n/a) (n/a) emp_id

CHECK Table Level valid_entered_by
(n/a) (n/a) Enabled Is_For_Replication ([entered_by]=suser_id() AND [entered_by]<>[emp_id])

CHECK Table Level valid_mgr
(n/a) (n/a) Enabled Is_For_Replication ([mgr_id]<>[emp_id] OR [emp_id]=(1))

Table is referenced by foreign key

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
master.dbo.employee: FK__employee__mgr_id__6ABBA185

1>
2> INSERT employee DEFAULT VALUES
3> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 2
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__employee__mgr_id__6ABBA185". The conflict occurred in database "master", table "dbo.employee", column "emp_id".
The statement has been terminated.
1>
2>
3> drop table employee;
4> GO
</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL/System/sp helpconstraint - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия