SQL constraint Commentary

SQL Constraint commentary 2009-04-27 09:29

constraints mainly include: Not NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT 1, NOT NULL: the content used to control the field must not be empty (null). How to use: Create table MyTable ( ID varchar (+) NOT NULL, Name varchar (32) ) 2, Unique: Control field content cannot be repeated, a table agrees to have multiple Unique constraints. In SQL Server, Orcale, MS Access-supportedJoin the Unique syntax： Create Table MyTable ( ID varchar (+) NOT NULLUnique, Name varchar (32) ) In SQL Server, My SQL supportedJoin the Unique syntax： Create Table MyTable ( ID varchar (+) NOT NULL, Name varchar (32), Unique(ID,.....) ) Supported by SQL Server, Orcale, MS Access, My sqlJoin the Unique syntax： Create Table MyTable ( ID varchar (+) NOT NULL, Name varchar (32), ConstraintUniqueName Unique(Uniquecolumn,.....) ) Note: The first way of writing (that is, adding a constraint directly behind a field) guarantees that each field data is unique. Another way of writing (that is, adding several fields as constraints at the same time), this is to ensure that several field dataat the same timeis unique, for example, the unique (id,name) Two field is a constraint, then when the ID has a repetition value, and the name does not repeat the value of the case is agreed, only if the two field data is repeated with the original data is not agreed. In SQL Server, Oracle, MS AccessDeleteUnique constraint syntax: Drop constraintUniqueName; In my SQLDeleteUnique constraint Syntax: Drop indexUniqueName; 3, Primary Key: Also used for control field content cannot be repeated, but it only agrees to appear in a table one. In SQL Server, Orcale, MS Access-supportedJoin Primary KeyGrammar： Create Table MyTB1 ( ID nvarchar (+) NOT NULL primary key, Name nvarchar (32) ) Join Primary Key syntax in SQL Server, My SQL support: Create Table MyTB1 ( ID nvarchar (+) NOT NULL, Name nvarchar (32), primary KEY (ID) ) In SQL Server, Orcale, MS Access, My SQL supportedAdd primary Key syntax： Create Table MyTB1 ( ID nvarchar (+) NOT NULL, Name nvarchar (32), constraint Primaryname primary key (ID) ) In cases where SQL Server, Orcale, MS Access, My SQL table already existPRIMARY KEY constraint syntax to join the table： Alter Table MyTB1 ADD Primary Key (id,......)--the system will define its own constraint name. Alter Table MyTB1 Add constaint Primaryname primary key (ID)--The ability to define constraint names on their own In SQL Server, Orcale, MS Access, delete the syntax of the Primary Key constraint that the table already exists: Alter Table MyTB1 Drop Constraint Primaryname The syntax for the Primary Key constraint is already present in the My SQL delete table: Alter Table MyTB1 Drop Primary Key Unique and Primary the same: Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection. the difference between Unique and Primary: Each table can have multiple UNIQUE constraints, but each table can have only one PRIMARY KEY constraint, and UNIQUE agrees to have a null value, and PRIMARY KEY does not agree to have a null value. Note: In the same database, even in a different table, the constraint name does not agree with the same. 4. The Foreign key:foreign key constraint is used to prevent the action of breaking the connection between tables, and the Foreign key constraint also prevents illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to. Join in SQL Server, My SQL supportForeign Key Syntax： Create Table MyTB1 ( ID nvarchar (+) NOT null primary key, Name nvarchar (32), foreign key (ID) references MYTB (ID) ) Join in SQL Server, Orcale, MS Access supportForeign Key Syntax： Create Table MyTB1 ( ID nvarchar (+) NOT NULLforeign key references MYTB (ID), Name nvarchar (32) ) Joins that are supported by SQL Server, Orcale, MS Access, My sqlForeign Key Syntax： Create Table MyTB1 ( ID nvarchar (+) NOT null primary key, Name nvarchar (32), Constraint ForeignNameforeign key (ID) references MYTB (ID) ) In the case of SQL Server, Orcale, MS Access, My SQL tables already exist, join the FOREIGN KEY constraint syntax to the table: Alter Table MyTB1 ADD foreign key (ID) references MYTB (ID)--this writes the system to define its own constraint name Alter Table MyTB1 Add Constraint foreignname foreign key (ID) references MYTB (ID)--Write yourself to define the constraint name yourself Syntax for deleting foreign KEY constraints in SQL Server, Orcale, MS Access: Alter Table MyTB1 Drop Constraint ForeignName; To remove the syntax for a FOREIGN KEY constraint in my SQL: Alter Table MyTB1 Drop foreign key foreignname; 5. Check: The range of values used to control the field. In SQL Server, My SQL supports the join check syntax : Create Table MyCheck               ( ID nvarchar (+) NOT NULL, The age int is not NULL, Check (age>15 and age <30) ) Join in SQL Server, Orcale, MS Access supportCheck Grammar： Create Table MyCheck ( ID nvarchar (+) NOT NULL, Age int NOT nullCheck (age>15 and age<30) ) Joins that are supported by SQL Server, Orcale, MS Access, My sqlCheck Syntax： Create Table MyCheck ( ID nvarchar (+) NOT NULL, The age int is not NULL, constraint checkname Check (age<15 and age>30) ) The syntax for adding a CHECK constraint to a table exists in the case of Sql Server, Orcale, MS Access, My SQL tables already exist: Alter Table MyCheck Add check (id= ' celly '); --this defines the check constraint name that the system defines itself. Alter Table MyCheck Add constraintCheckNameCheck (id= ' celly '); --This definition defines the CHECK constraint name yourself. In SQL Server, Orcale, MS Access removes the syntax for a check constraint that already exists for the table: Alter Table MyCheck drop Constraint CheckName 6. Default: Used to set defaults for new records. In SQL Server, Orcale, MS Access, My sql, add the default constraint syntax: Create Table Mydefault ( ID int, name nvarchar (+) default ' celly ' ) Add a field default to the existing table in my SQL: Alter Table Mydefault Alter [ID] Set default 0 Add field defaults to existing tables in SQL Server, Orcale, MS Access: Alter Table Mydefault Alter column [ID] set default 0 Delete the field default syntax in My SQL: Alter Table Mydefault AlterColumnNameDrop default