sql-primary key, foreign key, index

The role of the primary and foreign keys of sql:

Foreign key value rule: A null value or a reference primary key value.

(1) When a non-null value is inserted, it cannot be inserted if the value is not in the primary key table.

(2) When updating, you cannot change the value that is not in the primary key table.

(3) When you delete a primary key table record, you can either cascade Delete or reject it when you select a foreign key record while the foreign key is being constructed.

(4) When updating a primary key record, there is also a selection of cascading updates and rejected executions.

In short, the primary key and foreign key of SQL is the binding function.

There are several attributes in a record in a relational database, which can become a primary key if one of the attribute groups (note is a group) can uniquely identify a record.

Like what:

Student Table (School number, name, gender, Class)

Each student's school number is unique, the study number is a primary key;

Curriculum (course number, course name, credits)

Where the course number is unique, the course number is a primary key;

Score Table (School number, course number, score)

A single attribute in the score table cannot uniquely identify a record, and the combination of the study number and course number uniquely identifies a record, so the attribute group for the study number and course number is a primary key.

The school number in the score table is not the primary key of the score table, but it corresponds with the student's number, and the student's table is the primary key of the student table, the school number in the score table is the foreign key of the student table; Similarly, the course number in the score table is the foreign key of the curriculum.

Defining primary and foreign keys is primarily to maintain the integrity of the relational database, summarizing:

A primary key is a unique identifier that can determine a record, for example, a record includes a positive sign, name, and age. The ID number is the only person who can identify you, others may have duplicates, so the ID number is the primary key.

A foreign key is used to associate with another table. is a field that determines the record of another table, which is used to maintain data consistency. For example, a field in a table is the primary key of table B, so he can be a foreign key to table A.

Ii. differences between primary key, foreign key, and index

Defined:

Primary Key-uniquely identifies a record, cannot be duplicated, is not allowed to be empty

Foreign key--the foreign key of the table is the primary key of another table, the foreign key can have duplicate, can be a null value

Index--The field does not have duplicate values, but can have a null value

Role:

Primary key--to ensure data integrity

Foreign key--used to establish contact with other tables.

Index--is to improve the speed of query sorting

Number:

Primary key--the primary key can only have one

Foreign key--a table can have multiple foreign keys

Index--A table can have multiple unique indexes

Methods for creating primary key and FOREIGN KEY constraints for SQL:

CREATE TABLE Student--Build table format: The name of the form that is customized by the CREATE table

(--The field name is generally of a certain meaning in English

Studentname nvarchar (15),--Format: Field name Type () the length of the input is allowed in parentheses

studentage int,--int No need to connect the length after the type

Studentsex nvarchar (2)--no comma after the last field

)

--You can add constraints to a field when you create the table:

CREATE TABLE Student

(

Studentno int PRIMARY Key identity,--plus PRIMARY KEY constraint, and identity column properties (both constitute entity integrity)

Studentname nvarchar () not NULL,--add non-null constraint, no "NOT NULL" default: Can be empty

Studentschool text (FOREIGN key REFERENCES schooltable (Schoolname),--Add foreign KEY constraint, format: FOREIGN key REFERENCES associated table name (field name)

studentage int Default ((0)),--Add the defaults constraint

Studentsex nvarchar (2) Check (studentsex=n ' male ' or studentsex=n ' female ')--add CHECK constraint, format: check (conditional expression)

)

--If the table is created and then constrained, the format is:

--PRIMARY key:

ALTER TABLE table name

Add constraint pk_ field name--"PK" is the abbreviation for the primary key, the field name is the name of the field on which to create the main key, ' pk_ field name ' is the constraint name

Primary key (field name)--field name ibid.

--Unique constraint:

ALTER TABLE table name

Add Constraint uq_ field name

Unique (field name)

--FOREIGN KEY constraint:

ALTER TABLE table name

Add constraint fk_ field name--"FK" is the abbreviation for the foreign key

Foreign key (field name) references associated table name (associated field name)--note ' associated table name ' and ' Associated field name '

ALTER TABLE Table A ADD Constraint Fk_b foreign key (ticket_no) References table B (TICKET_NO)

ALTER TABLE Table A ADD Constraint Fk_c foreign key (person_no) References table C (PERSON_NO)

ALTER TABLE score table ADD constraint Fk_studentno foreign key (Studentno) references Student (STUDENTNO)

On UPDATE CASCADE on DELETE CASCADE

Cascade UPDATE, Cascade Delete, so that when the main table student is deleted, all scores for that student in the score table will be deleted.

--CHECK constraints:

ALTER TABLE table name

Add Constraint ck_ field name

Check (conditional expression)--the condition in the conditional expression is concatenated with the relational operator

--Default value constraint:

ALTER TABLE table name

Add Constraint df_ field name

Default ' defaults ' for field name--where ' default ' is the value you want to default, note ' for '

--delete the created constraint:

ALTER TABLE table name

DROP constraint constraint name--the constraint name is the constraint name you created earlier, such as: Pk_ field

--NOTE: If the constraint was created when the table was created, it cannot be deleted with a command

--can only be deleted in ' Enterprise Manager '

--Get the table structure in SQL Server

SELECT syscolumns.name,systypes.name,syscolumns.isnullable,

Syscolumns.length

From Syscolumns,systypes

WHERE Syscolumns.xusertype = Systypes.xusertype

and syscolumns.id = object_id (' Student ')

--Singleton query table increment field

SELECT [name] from syscolumns WHERE

id = object_id (N ' Student ') and ColumnProperty (Id,name, ' isidentity ') =1

--Get Table primary FOREIGN KEY constraint

EXEC sp_helpconstraint ' Sturesults '

--Query table primary key foreign key information

SELECT sysobjects.id objectid,object_name (sysobjects.parent_obj) TableName,

Sysobjects.name ConstraintName, Sysobjects.xtype as ConstraintType,

Syscolumns.name as ColumnName

From sysobjects INNER JOIN sysconstraints

On Sysobjects.xtype in (' C ', ' F ', ' PK ', ' UQ ', ' D ')

and sysobjects.id = Sysconstraints.constid

Left OUTER JOIN syscolumns on sysconstraints.id = syscolumns.id

WHERE object_name (sysobjects.parent_obj) = ' Sturesults '

sql-primary key, foreign key, index