SQL约束与索引

1.NOT NULL约束NOT NULL，它确保列必须具有一个值。
create table MyTable
(
Column1 int not null,
Column2 varchar(20),
Column3 varchar(12) not null
);

在创建一个表之后，如果希望使得一个或多个列服从NOT NULL约束，可使用。
alter table MyTable modify Column2 varchar(20) not null;

如果希望一个包含NULL记录的列服从NOT NULL约束，可使用。
update MyTable set Column2=’  ’ where Column2 is null;

 2.UNIQUE约束UNIQUE约束防止一个特定的列中的两个记录具有一样的值。例如，不希望两个或多个人具有相同的Email地址。
可在列的类型定义之后添加对表定义的UNIQUE约束。
create table MyUniqueTable
(
Column1 int,Column2 varchar(20) unique,Column3 varchar(12) unique);

上述SQL语句创建了一个名为MyUniqueTable的表，并对列Column2和Column3添加了UNIQUE约束。
当你尝试下面的插入语句。
insert into MyUniqueTable(Column1,Column2,Column3) values (123,’ABC’,’DEF’);//OK
insert into MyUniqueTable(Column1,Column2,Column3) values (123,’XYZ’,’DEF’);//ERROR

很明显，第二条插入语句的Column3的值重复了。
下面的示例创建一个新表，并添加了一个名为MyUniqueConstraint的约束，这个约束指定Column2和Column3的组合必须唯一。
create table AnotherTable
(
Column1 int,
Column2 varchar(20),
Column3 varchar(12),constraint MyUniqueConstraint unique (Column2,Column3)//注意是两个列的组合唯一。);

当你尝试下面的插入语句。
insert into AnotherTable(Column1,Column2,Column3) values (1,’ABC’,’DEF’);//OK
insert into AnotherTable(Column1,Column2,Column3) values (2,’ABC’,’XYZ’);//OK
insert into AnotherTable(Column1,Column2,Column3) values (3,’DEF’,’XYZ’);//OK
insert into AnotherTable(Column1,Column2,Column3) values (4,’ABC’,’DEF’);//ERROR

很明显，第四条插入语句与第一条插入语句重复了。
可以对一个表添加多个约束，只要每个约束具有一个不同的名称。
create table AnotherTable
(
Column1 int,
Column2 varchar(20),
Column3 varchar(12),constraint MyUniqueConstraint unique (Column2,Column3),constraint AnotherConstraint unique(Column1,Column3));

在创建表之后，使用ALTER TABLE语句，可以添加和删除UNIQUE约束。
alter table AnotherTable add constraint MyUniqueConstraint unique (Column2,Column3);
alter table AnotherTable drop constraint MyUniqueConstraint;

 3.CHECK约束CHECK约束检查输入到记录中的值是否满足一个条件。如果条件为假，则记录违反约束，并不被输入到表中。例如，可以约束存储年龄的列中的值大于等于零。CHECK条件可以是任意有效的SQL条件，类似于WHERE子句中的条件。
create table NamesAges
(
Name varchar(50),
Age int check (Age>=0)
);

当你尝试下面的插入语句。
insert into NamesAges (Name,Age) values (‘Jim’,30);//OK
insert into NamesAges(Name,Age) values(‘Tom’);//OK
insert into NamesAges(Name,Age) values(‘Jack’,-22)//ERROR

Age列的条件必须为true或unknown。语句一可执行，因为CHECK条件为true；语句二可执行，因为CHECK条件为unknown；语句三不可执行，因为CHECK条件为false。
为了阻止语句二的执行，你可以这样做。
create table NamesAges
(
Name varchar(50),
Age int not null check (Age>=0)
);

添加一个CHECK子句到列定义，条件只能够检查该列。如果你想执行下面的语句，会报错。
create table Employee
(
EmployeeName varchar(50),
AvgMonthlyWage decimal(12,2) check (AvgMonthlyWage>HourlyRate),
HpurlyRate decimal(12,2)
);//ERROR

如果希望CHECK条件子句包含表中的多个列，则需要在列定义的末尾定义它。
create table Employee
(
EmployeeName varchar(50),
AvgMonthlyWage decimal(12,2),
HourlyRate decimal(12,2),
constraint HourlyLess check (AvgMonthlyWage>HourlyRate)
);

如果希望在创建表之后，添加CHECK约束。
alter table Employee add constraint HourlyLess check(AvgMonthlyWage>HourlyRate);

删除现有的约束（MySQL中无效）。
alter table Employee drop constraint HourlyLess;

 4.主键和PRIMARY KEY约束主键必须唯一标识一个记录，这意味着仅可以包含唯一值，而不能包含NULL值。事实上，PRIMARY KEY约束是UNIQUE约束与NOT NULL约束的组合。每个表中只能有一个PRIMARY KEY约束。
create table HolidayBookings
(
CustomerId int primary key,
BookingId int,
Destination varchar(50)
);

我们也可以建立一个PRIMARY KEY约束。
create table MoreHolidayBookings
(
CustomerId int not null,
BookingId int not null,
Destination varchar(50),
constraint booking_pk primary key (CustomerId,BookingId)
);

这里booking_pk约束是一个PRIMARY KEY约束，CustomerId列和BookingId列的组合是主键列，即它们的组合必须唯一。
下面尝试这些插入语句。
insert into MoreHolidayBookings(CustomerId,BookingId,Destination) values (1,1,’China’);//OK
insert into MoreHolidayBookings(CustomerId,BookingId,Destination) values (1,2,’Canada’);//OK
insert into MoreHolidayBookings(CustomerId,BookingId,Destination) values (2,2,’Japan’);//OK
insert into MoreHolidayBookings(CustomerId,BookingId,Destination) values (1,1,’U’);//ERROR
insert into MoreHolidayBookings(CustomerId,Destination) values (3,’Mexico’);//ERROR

语句四中的主键与语句一重复；语句五中的主键值之一（BookingId）为NULL。
也可以在一个现有表中添加主键，但必须保证组成主键的列必须包含NOT NULL约束。
alter table MoreHolidayBookings
add constraint more_holiday_pk primary key(CustomerId,BookingId);

删除主键约束。
alter table MoreHolidayBookings drop constraint more_holiday_pk;

在MySQL中。
alter table MoreHolidayBookings drop primary key;

 5.外键外键是访问另外一个表中的主键的列。主键和外键创建了不同表中数据的相互关系。SQL的外键约束允许你指明一个表中的一个列是另外一个表中的外键，即可以通过一个表中一个列来引用另外一个表中的记录行。创建外键的基本语法如下。
ALTER TABLE name_of_table_to_add_foreign_key
ADD CONSTRAINT name_of_foreign_key
FOREIGN KEY (name_of_column_that_is_foreign_key_column)
REFERENCES name_of_table_that_is_referenced(name_of_column_being_referenced);

example:
TABLE Location：
LocationId,Street,City,State
TABLE Attendance：
LocationId , MeetingDate, MenberAttended ,MemberId, ,

创建一个FOREIGN KEY约束。
alter table Location alter cloumn LocationId int not null;//设定Location.LocationId非空。
alter table Location
add constraint locationid_pk primary key(LocationId);//设定Location.LocationId为主键。
alter table Attendance
add constraint locationid_fk
foreign key (LocationId)
references Location(LocationId);//设定Attendance.LocationId为外键。

在上面的例子中，匹配外键的主键仅由一列组成。可以由多列组成。
alter table SomeTable
add constraint SomeTable_fk1
foreign key (EmployeeName,EmployeeId,MemberShipId)
references SomePrimarKeyTable (EmployeeName,EmployeeId,MemberShipId);

也可以在创建表时，定义一个FOREIGN KEY。
create table Attendance
(
LocationId int,
MeetingDate date,
MenberAttended char(1),
MemberId int,
constraint SomeTable_fk1
foreign key(LocaionId)
references Location(LocationId)
);

 6.索引创建索引需要使用CREATE INDEX语句。语法如下：
CREATE INDEX <index_name> ON <table_name> (<column_names>);

example:
create index member_name_index on MemberDetails (FirstName,LastName);

这样，查询的结果就会是排过序的。默认情况下，索引按照升序排列结果。
如果需要删除索引（MySQL中）。
alter table MemberDetails drop index member_name_index;

当创建索引时，可以设置两个其他的选项。第一个是UNIQUE选项，它防止输入重复的值，其作用类似于UNIQUE约束。第二个选项确定列的顺序。默认为升序，可设置为降序。下例中，展示先按照姓的降序排列，如果姓相同按照名的升序排列。
create unique index member_name_index
on MemberDetails (LastName desc,FirstName);

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/bovy/archive/2007/11/16/1887991.aspx