SQL Getting Started Classic (v) Keys and constraints

This blog mainly focuses on the creation of keys, the creation of constraints. Modify objects and delete objects.

PRIMARY KEY : The primary key is a unique identifier for each row and must contain a unique value (and therefore cannot be null). Because the primary key is important in the relational database, it is the most significant of all the keys and constraints. A table can have a maximum of one primary key. There are few tables that do not require a primary key. The primary key declaration is unique. There are commonly used identity autogrow values and GUIDs. The primary key is a unique identity value. If the customer operates a table that does not have a primary key. Add the same data 2 times in a row. How do you delete. Delete always 2 strips one delete. Let's move on to the blog post.

Create a table of primary keys below:

 UsePandaGO   --Switch to panda database operationCREATE TABLEtest002 (IDint Identity(1,1)Primary Key,--Create a primary keyNamenvarchar( -) not NULL, Ageint                default(0)Check(age>=0)--default values and CHECK constraints are created)

Table structure query, execute EXEC sp_helpconstraint test002, query to 3. Pk__test002__7c8480ae is the primary key for this table.

We remove this primary key at the re-create primary key:

 ALTER TABLEtest002Drop  CONSTRAINTPk__test002__7c8480ae--Pk__test002__7c8480ae This database is generated by default. We delete her, create it with a scriptGO--tell the database to execute the preceding statementALTER TABLEtest002ADD  CONSTRAINTpk_test002_idPRIMARY  KEY(ID)--Create a primary keyGOexecSp_helpconstraint test002---The result is still 3. The primary key name has been changed to pk_test002_id

First, it's easier.

foreign keys: foreign keys ensure data integrity, as well as the relationship between tables, and if you add a foreign key to a table, you create a dependency between the table that you define the foreign key table (the reference table) and the foreign key reference (the referenced table). After the foreign key is added, inserting the reference table data must match a record with the referenced table reference column data.

Try: Create a table with a foreign key. We create a test003 table in the panda data

CREATE TABLEtest003 (IDint Identity(1,1)Primary Key, Test002idint     Foreign Key Referencestest002 (ID),--Create a foreign keyOrderDatedatetime  default(getdate()), namenvarchar( -) not NULL )GO execSp_helpconstraint test003--query to system default generate FK__TEST003__TEST002__023D5A04 name foreign key

A FOREIGN KEY constraint can create 0-253 in a table. A PRIMARY KEY constraint can be only one, and a foreign KEY constraint may be multiple. Only foreign key values can be referenced per column. is similar to the primary key creation method. Delete almost. Try creating a foreign key using a script: I'm going to delete the table above. Foreign key

ALTER TABLEtest003Drop CONSTRAINTFk__test003__test002__023d5a04.--Delete foreign keyGOALTER TABLEtest003ADD CONSTRAINTfk_t003_t002_idFOREIGN KEY(TEST002ID)REFERENCEStest002 (ID)--create foreign key, reference test002 ID foreign key

Go

execsp_helpconstraint test003--foreign key information has been updated to fk_t003_t002_id

is not as simple as it looks; the foreign key is optional cascade operation. Foreign keys are bidirectional, restricting not only the existence of a child table value, but also the parent table. Each time a parent table is deleted, the child table is checked for data (Preventing child table orphaned data).

CREATE TABLEtest003 (IDint Identity(1,1)Primary Key, Test002idint     Foreign Key Referencestest002 (ID) on UPDATE  notACTIONOn DELETE CASCADE,   ,--Create a foreign keyOrderDatedatetime  default(getdate()), namenvarchar( -) not NULL )---described below. A foreign key on the back can follow 4 parameters. SET null,set default,not ACTION, CASCADE

Not action: Execution of the delete and update operations is always rejected because subset data cannot be updated to prevent data isolation and integrity. So the quoted table is executed when it is rejected.

CASCADE: Delete and update will update subset data. Deleting the referenced table data propagates to the reference table data, and updates and deletes the child table data together for data integrity and to not isolate the data.

Set default and set NULL: If the referenced table is deleted. Referencing the table foreign key data sets a default value or null.

Unique Uniqueness Constraint : These constraints are relatively simple. A unique constraint can be a candidate primary key. is similar to the primary key. They have something in common. The specified column in the table has only a unique specified value. The only difference. A unique constraint is not considered a unique identifier in a table. A table can have multiple unique constraints, with only one primary key. If you insert 2 identical UNIQUE constraint values, SQL rejects the execution. Give it a try:

ALTER TABLEtest002AddEmailnvarchar( -) not NULL   Unique,--Create a UNIQUE constraintPhonenvarchar( A) not NULLGOALTER TABLEtest002Add constraintUk_test002_phoneUNIQUE(phone)--Create a unique constraint for the phone fieldexecSp_helpconstraint test002--you'll find 2 more unique constraints. 

Check constraint:check constraint good She is not limited to a special column. A check constraint can be associated with a column, but it can also be associated with a table, and you can define a check constraint by using a more where clause.

Limit the month CHECK (between 1 and 12), the correct mobile phone number ([1][3-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]), and so on.

How to add a check constraint to a table. TEST002 Age has check>0 constraints. We create a constraint for the OrderDate in the test003 table that cannot be less than the current date

ALTER TABLE test003 ADD CONSTRAINT ck_test003_date CHECK (OrderDate>=getdate())

default constraint: As with all constraints, comparisons are confusing. Default can only be used with insert add statements. If no value is provided, the default value is. If the column value is provided, the default value is not used.

How to add a default constraint to a table. test002 Table OrderDate has default=getdate () constraints. We add a default value of "anonymous user" to the name in the test003 table

SQL Getting Started Classic (v) Keys and constraints