SQL约束.docx
《SQL约束.docx》由会员分享,可在线阅读,更多相关《SQL约束.docx(69页珍藏版)》请在冰点文库上搜索。
SQL约束
SQL约束
约束用于限制加入表的数据的类型。
可以在创建表时规定约束(通过CREATETABLE语句),或者在表创建之后也可以(通过ALTERTABLE语句)。
我们将主要探讨以下几种约束:
NOTNULL
UNIQUE
PRIMARYKEY
FOREIGNKEY
CHECK
DEFAULT
SQLNOTNULL约束
NOTNULL约束强制列不接受NULL值。
NOTNULL约束强制字段始终包含值。
这意味着,如果不向字段添加值,就无法插入新纪录或者更新记录。
下面的SQL语句强制"Id_P"列和"LastName"列不接受NULL值:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255)
)
SQLUNIQUE约束
UNIQUE约束唯一标识数据库表中的每条记录。
UNIQUE和PRIMARYKEY约束均为列或列集合提供了唯一性的保证。
PRIMARYKEY拥有自动定义的UNIQUE约束。
请注意,每个表可以有多个UNIQUE约束,但是每个表只能有一个PRIMARYKEY约束。
SQLUNIQUEConstraintonCREATETABLE
下面的SQL在"Persons"表创建时在"Id_P"列创建UNIQUE约束:
MySQL:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255),
UNIQUE(Id_P)
)
SQLServer/Oracle/MSAccess:
CREATETABLEPersons
(
Id_PintNOTNULLUNIQUE,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255)
)
如果需要命名UNIQUE约束,以及为多个列定义UNIQUE约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255),
CONSTRAINTuc_PersonIDUNIQUE(Id_P,LastName)
)
SQLUNIQUEConstraintonALTERTABLE
当表已被创建时,如需在"P_Id"列创建UNIQUE约束,请使用下列SQL:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
ADDUNIQUE(P_Id)
如需命名UNIQUE约束,并定义多个列的UNIQUE约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
ADDCONSTRAINTuc_PersonIDUNIQUE(P_Id,LastName)
撤销UNIQUE约束
如需撤销UNIQUE约束,请使用下面的SQL:
MySQL:
ALTERTABLEPersons
DROPINDEXuc_PersonID
SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
DROPCONSTRAINTuc_PersonID
SQLPRIMARYKEY约束
PRIMARYKEY约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含NULL值。
每个表都应该有一个主键,并且每个表只能有一个主键。
SQLPRIMARYKEYConstraintonCREATETABLE
下面的SQL在"Persons"表创建时在"Id_P"列创建PRIMARYKEY约束:
MySQL:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255),
PRIMARYKEY(Id_P)
)
SQLServer/Oracle/MSAccess:
CREATETABLEPersons
(
Id_PintNOTNULLPRIMARYKEY,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255)
)
如果需要命名PRIMARYKEY约束,以及为多个列定义PRIMARYKEY约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255),
CONSTRAINTuc_PersonIDPRIMARYKEY(Id_P,LastName)
)
SQLPRIMARYKEYConstraintonALTERTABLE
如果在表已存在的情况下为"Id_P"列创建PRIMARYKEY约束,请使用下面的SQL:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
ADDPRIMARYKEY(Id_P)
如果需要命名PRIMARYKEY约束,以及为多个列定义PRIMARYKEY约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
ADDCONSTRAINTpk_PersonIDPRIMARYKEY(Id_P,LastName)
注释:
如果您使用ALTERTABLE语句添加主键,必须把主键列声明为不包含NULL值(在表首次创建时)。
撤销PRIMARYKEY约束
如需撤销PRIMARYKEY约束,请使用下面的SQL:
MySQL:
ALTERTABLEPersons
DROPPRIMARYKEY
SQLServer/Oracle/MSAccess:
SQLFOREIGNKEY约束
一个表中的FOREIGNKEY指向另一个表中的PRIMARYKEY。
让我们通过一个例子来解释外键。
请看下面两个表:
"Persons"表:
Id_PLastNameFirstNameAddressCity
1AdamsJohnOxfordStreetLondon
2BushGeorgeFifthAvenueNewYork
3CarterThomasChanganStreetBeijing
"Orders"表:
Id_OOrderNoId_P
1778953
2446783
3224561
4245621
请注意,"Orders"中的"Id_P"列指向"Persons"表中的"Id_P"列。
"Persons"表中的"Id_P"列是"Persons"表中的PRIMARYKEY。
"Orders"表中的"Id_P"列是"Orders"表中的FOREIGNKEY。
FOREIGNKEY约束用于预防破坏表之间连接的动作。
FOREIGNKEY约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
SQLFOREIGNKEYConstraintonCREATETABLE
下面的SQL在"Orders"表创建时为"Id_P"列创建FOREIGNKEY:
MySQL:
CREATETABLEOrders
(
O_IdintNOTNULL,
OrderNointNOTNULL,
Id_Pint,
PRIMARYKEY(O_Id),
FOREIGNKEY(Id_P)REFERENCESPersons(Id_P)
)
SQLServer/Oracle/MSAccess:
CREATETABLEOrders
(
O_IdintNOTNULLPRIMARYKEY,
OrderNointNOTNULL,
Id_PintFOREIGNKEYREFERENCESPersons(Id_P)
)
如果需要命名FOREIGNKEY约束,以及为多个列定义FOREIGNKEY约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
CREATETABLEOrders
(
O_IdintNOTNULL,
OrderNointNOTNULL,
Id_Pint,
PRIMARYKEY(O_Id),
CONSTRAINTfk_PerOrdersFOREIGNKEY(Id_P)
REFERENCESPersons(Id_P)
)
SQLFOREIGNKEYConstraintonALTERTABLE
如果在"Orders"表已存在的情况下为"Id_P"列创建FOREIGNKEY约束,请使用下面的SQL:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEOrders
ADDFOREIGNKEY(Id_P)
REFERENCESPersons(Id_P)
如果需要命名FOREIGNKEY约束,以及为多个列定义FOREIGNKEY约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEOrders
ADDCONSTRAINTfk_PerOrders
FOREIGNKEY(Id_P)
REFERENCESPersons(Id_P)
撤销FOREIGNKEY约束
如需撤销FOREIGNKEY约束,请使用下面的SQL:
MySQL:
ALTERTABLEOrders
DROPFOREIGNKEYfk_PerOrders
SQLServer/Oracle/MSAccess:
ALTERTABLEOrders
DROPCONSTRAINTfk_PerOrders
SQLCHECK约束
CHECK约束用于限制列中的值的范围。
如果对单个列定义CHECK约束,那么该列只允许特定的值。
如果对一个表定义CHECK约束,那么此约束会在特定的列中对值进行限制。
SQLCHECKConstraintonCREATETABLE
下面的SQL在"Persons"表创建时为"Id_P"列创建CHECK约束。
CHECK约束规定"Id_P"列必须只包含大于0的整数。
MySQL:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255),
CHECK(Id_P>0)
)
SQLServer/Oracle/MSAccess:
CREATETABLEPersons
(
Id_PintNOTNULLCHECK(Id_P>0),
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255)
)
如果需要命名CHECK约束,以及为多个列定义CHECK约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255),
CONSTRAINTchk_PersonCHECK(Id_P>0ANDCity='Sandnes')
)
SQLCHECKConstraintonALTERTABLE
如果在表已存在的情况下为"Id_P"列创建CHECK约束,请使用下面的SQL:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
ADDCHECK(Id_P>0)
如果需要命名CHECK约束,以及为多个列定义CHECK约束,请使用下面的SQL语法:
MySQL/SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
ADDCONSTRAINTchk_PersonCHECK(Id_P>0ANDCity='Sandnes')
撤销CHECK约束
如需撤销CHECK约束,请使用下面的SQL:
SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
DROPCONSTRAINTchk_Person
SQLDEFAULT约束
DEFAULT约束用于向列中插入默认值。
如果没有规定其他的值,那么会将默认值添加到所有的新纪录。
SQLDEFAULTConstraintonCREATETABLE
下面的SQL在"Persons"表创建时为"City"列创建DEFAULT约束:
MySQL/SQLServer/Oracle/MSAccess:
CREATETABLEPersons
(
Id_PintNOTNULL,
LastNamevarchar(255)NOTNULL,
FirstNamevarchar(255),
Addressvarchar(255),
Cityvarchar(255)DEFAULT'Sandnes'
)
通过使用类似GETDATE()这样的函数,DEFAULT约束也可以用于插入系统值:
CREATETABLEOrders
(
Id_OintNOTNULL,
OrderNointNOTNULL,
Id_Pint,
OrderDatedateDEFAULTGETDATE()
)
SQLDEFAULTConstraintonALTERTABLE
如果在表已存在的情况下为"City"列创建DEFAULT约束,请使用下面的SQL:
MySQL:
ALTERTABLEPersons
ALTERCitySETDEFAULT'SANDNES'
SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
ALTERCOLUMNCitySETDEFAULT'SANDNES'
撤销DEFAULT约束
如需撤销DEFAULT约束,请使用下面的SQL:
MySQL:
ALTERTABLEPersons
ALTERCityDROPDEFAULT
SQLServer/Oracle/MSAccess:
ALTERTABLEPersons
CREATEINDEX语句用于在表中创建索引。
在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据。
索引
您可以在表中创建索引,以便更加快速高效地查询数据。
用户无法看到索引,它们只能被用来加速搜索/查询。
注释:
更新一个包含索引的表需要比更新一个没有索引的表更多的时间,这是由于索引本身也需要更新。
因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。
SQLCREATEINDEX语法
在表上创建一个简单的索引。
允许使用重复的值:
CREATEINDEXindex_name
ONtable_name(column_name)
注释:
"column_name"规定需要索引的列。
SQLCREATEUNIQUEINDEX语法
在表上创建一个唯一的索引。
唯一的索引意味着两个行不能拥有相同的索引值。
CREATEUNIQUEINDEXindex_name
ONtable_name(column_name)
CREATEINDEX实例
本例会创建一个简单的索引,名为"PersonIndex",在Person表的LastName列:
CREATEINDEXPersonIndex
ONPerson(LastName)
如果您希望以降序索引某个列中的值,您可以在列名称之后添加保留字DESC:
CREATEINDEXPersonIndex
ONPerson(LastNameDESC)
假如您希望索引不止一个列,您可以在括号中列出这些列的名称,用逗号隔开:
CREATEINDEXPersonIndex
ONPerson(LastName,FirstName)
通过使用DROP语句,可以轻松地删除索引、表和数据库。
SQLDROPINDEX语句
我们可以使用DROPINDEX命令删除表格中的索引。
用于MicrosoftSQLJet(以及MicrosoftAccess)的语法:
DROPINDEXindex_nameONtable_name用于MSSQLServer的语法:
DROPINDEXtable_name.index_name用于IBMDB2和Oracle语法:
DROPINDEXindex_name用于MySQL的语法:
ALTERTABLEtable_nameDROPINDEXindex_nameSQLDROPTABLE语句
DROPTABLE语句用于删除表(表的结构、属性以及索引也会被删除):
DROPTABLE表名称SQLDROPDATABASE语句
DROPDATABASE语句用于删除数据库:
DROPDATABASE数据库名称SQLTRUNCATETABLE语句
如果我们仅仅需要除去表内的数据,但并不删除表本身,那么我们该如何做呢?
请使用TRUNCATETABLE命令(仅仅删除表格中的数据):
TRUNCATETABLE表名称
ALTERTABLE语句
ALTERTABLE语句用于在已有的表中添加、修改或删除列。
SQLALTERTABLE语法
如需在表中添加列,请使用下列语法:
ALTERTABLEtable_name
ADDcolumn_namedatatype
要删除表中的列,请使用下列语法:
ALTERTABLEtable_name
DROPCOLUMNcolumn_name
注释:
某些数据库系统不允许这种在数据库表中删除列的方式(DROPCOLUMNcolumn_name)。
要改变表中列的数据类型,请使用下列语法:
ALTERTABLEtable_name
ALTERCOLUMNcolumn_namedatatype
原始的表(用在例子中的):
Persons表:
IdLastNameFirstNameAddressCity
1AdamsJohnOxfordStreetLondon
2BushGeorgeFifthAvenueNewYork
3CarterThomasChanganStreetBeijing
SQLALTERTABLE实例
现在,我们希望在表"Persons"中添加一个名为"Birthday"的新列。
我们使用下列SQL语句:
ALTERTABLEPersons
ADDBirthdaydate
请注意,新列"Birthday"的类型是date,可以存放日期。
数据类型规定列中可以存放的数据的类型。
新的"Persons"表类似这样:
IdLastNameFirstNameAddressCityBirthday
1AdamsJohnOxfordStreetLondon
2BushGeorgeFifthAvenueNewYork
3CarterThomasChanganStreetBeijing
改变数据类型实例
现在我们希望改变"Persons"表中"Birthday"列的数据类型。
我们使用下列SQL语句:
ALTERTABLEPersons
ALTERCOLUMNBirthdayyear
请注意,"Birthday"列的数据类型是year,可以存放2位或4位格式的年份。
DROPCOLUMN实例
接下来,我们删除"Person"表中的"Birthday"列:
ALTERTABLEPerson
DROPCOLUMNBirthdayPersons表会成为这样:
IdLastNameFirstNameAdd