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:

Use panda GO   -Switch to Panda database operation create TABLE test002 (   ID  int identity (primary key),--creating primary key   name  nvarchar () not  null, age     int default                (0)  Check (age>=0)--Created defaults and CHECK constraints

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 TABLE test002 Drop  CONSTRAINT pk__test002__7c8480ae--pk__test002__7c8480ae This database is generated by default. We delete her, create go--with a script to tell the database to execute the preceding statement alter TABLE Test002add  CONSTRAINT pk_test002_idprimary  Key (ID)--Create primary key goexec  Sp_helpconstraint test002---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 TABLE test003 (   ID int identity (primary) key,   test002id  int     foreign key references test002 ( ID),--create foreign key   OrderDate  datetime  Default (GETDATE ()),   name         nvarchar () not null) GO exec  sp_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 table Test003drop CONSTRAINT fk__test003__test002__023d5a04.--Delete foreign key goalter TABLE Test003add CONSTRAINT fk_t003_ T002_idforeign key (Test002id) REFERENCES test002 (ID)--Create foreign key, reference test002 ID foreign key



EXEC sp_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 TABLE test003 (   ID int identity (primary) key,   test002id  int     foreign key references test002 (ID On   the UPDATE not ACTION on   DELETE CASCADE,   ,--Create a foreign key   OrderDate  datetime  Default (GETDATE ()),   name         nvarchar () 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 TABLE test002add   email  nvarchar (a) NOT null   unique,--create UNIQUE constraint   phone  nvarchar Nullgoalter TABLE test002add constraint  uk_test002_phoneunique (phone)-Create a unique constraint for the phone field exec sp_helpconstraint test002--you will 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 test003add CONSTRAINT Ck_test003_datecheck (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

ALTER TABLE test002add CONSTRAINT d_test002_namedefault (' anonymous user ') for name

Invalid data is ignored when creating constraints. Because a lot of data has been entered, the CHECK constraint now established cannot be used for the previous constraint. With Nocheck field.

We add constraints to test002 data

ALTER TABLE test002add constraint ck_test002_phonecheck (phone like ' [1][3-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] [0-9] ')--throw error message 547, Level 16, State 0, line 1th the ALTER TABLE statement conflicts with the CHECK constraint "Ck_test002_phone". This conflict occurs in the database "Panda", the Table "dbo.test002", >column,phone because the data already exist different data such as solve it? With Nocheck on the add front you can alter TABLE test002with nocheckadd constraint ck_test002_phonecheck (phone like ' [1][3-9][0-9][ 0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]--The command has been successfully completed.

To disable temporary CHECK constraint data:

ALTER TABLE test002nocheckconstraint ck_test002_phone--is available. Flag This constraint has been disabled by alter TABLE Test002checkconstraint ck_test002_phone--to re-enable this constraint.

Rules and default values:

Rule creation: Create Rule <rule name> as <expression>.

  Create Rule Salaryas @Salary >0;--creating rules
--View rules: Exec sp_helptext <rule name>; displays text messages

Bind the rules to the data column syntax: EXEC sp_bindrule ' <rule name> ', ' < object name> ', ' <futureonly _flag> '. such as: exec sp_bindrule ' Salary ', ' dbo.test002.age '.

Rules for removing bindings: Exec sp_unbindrule ' < object name> ' such as: exec sp_unbindrule ' dbo.test002.age '

Delete Rules drop rule <object name>

Default value created : Create default <default name> as default (default value).

CREATE DEFAULT  Salarydefaultas 0;

Binding default value: Exec sp_bindefault ' <rule name> ', ' < object name> ', ' <futureonly _flag> '.

Remove default values for bindings: exec sp_unbindefault ' <obect name ' >

Delete defaults: Drop default <default name>

Determine which columns use rules and default values: Exec sp_depends <object name>.

How to choose a data integrity decision:

SQL Getting Started Classic (v) Keys and constraints