Go Primary key and FOREIGN KEY constraints for SQL

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 TableStudent--Build Table Type: CREATE TABLE custom tables name( --field names are generally of a certain meaning in EnglishStudentnamenvarchar( the),--Format: Field name Type () the length of the input is allowed in parenthesesStudentageint,--the length of the int type does not need to be followedStudentsexnvarchar(2)--do not leave a comma after the last field) --when you create a table, you can add constraints to the fields:Create TableStudent (Studentnoint PRIMARY KEY IDENTITY(1,1),--The PRIMARY KEY constraint, and the identity column attribute (both of which constitute entity integrity)Studentnamenvarchar( the) not NULL,--add non-null constraint, no "NOT NULL" defaults to: Can be emptyStudentschooltext( -)FOREIGN KEY REFERENCESSchooltable (Schoolname),--Add foreign KEY constraint, format: FOREIGN key REFERENCES associated table name (field name)Studentageint DEFAULT((0)),--Add default value constraintStudentsexnvarchar(2)CHECK(Studentsex=N'male' orStudentsex=N'female')--add CHECK Constraint, format: check (conditional expression)) --If you add the constraint after the table is created, the format is: --PRIMARY Key:Alter TableTable nameAdd constraintPk_ Field Name--"PK" is the abbreviation for the primary key, and the field name is the name of the field on which to create the main key, and the ' pk_ field name ' is the constraint namePrimary Key(Field name)--field name Ibid . --UNIQUE constraint:Alter TableTable nameAdd constraintuq_ Field nameUnique(field name)--FOREIGN KEY constraints:Alter TableTable nameAdd constraintFk_ Field Name--"FK" is the abbreviation for the foreign keyForeign Key(Field name)ReferencesAssociated table name (associated field name)--Note ' Associated table name ' and ' associated field name ' Alter TableTable AAdd constraintFk_bForeign Key(TICKET_NO)ReferencesTable B (ticket_no)Alter TableTable AAdd constraintFk_cForeign Key(PERSON_NO)ReferencesTable C (person_no)Alter TableScore TableAdd constraintFk_studentnoForeign Key(Studentno)ReferencesStudent (Studentno) on UPDATE CASCADE  on DELETE CASCADECascade 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 TableTable nameAdd constraintck_ Field nameCheck(conditional expression)--conditions in conditional expressions are concatenated with relational operators --default value constraint:Alter TableTable nameAdd constraintdf_ Field namedefault 'Default Value'  forField name--where ' default ' is the value you want to default, note ' for ' --to delete a created constraint:Alter TableTable nameDrop constraintConstraint 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 the command--can only be deleted in ' Enterprise Manager '     --get the table structure in SQL ServerSELECTSyscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length fromSyscolumns,systypesWHERESyscolumns.xusertype=Systypes.xusertype andSyscolumns.id= object_id('Student') --Singleton query table increment fieldSELECT [name]  fromsyscolumnsWHEREID= object_id(N'Student') and ColumnProperty(Id,name,'isidentity')=1 --Get table primary FOREIGN KEY constraintEXECSp_helpconstraint'Sturesults'  --Querying table Primary key foreign key informationSELECTSysobjects.id ObjectId,object_name(sysobjects.parent_obj) tablename,sysobjects.name constraintname, Sysobjects.xtype asConstrainttype,syscolumns.name asColumnName fromsysobjectsINNER JOINsysconstraints onSysobjects.xtypeinch('C','F','PK','UQ','D') andSysobjects.id=Sysconstraints.constid Left OUTER JOINsyscolumns onSysconstraints.id=syscolumns.idWHERE object_name(sysobjects.parent_obj)='Sturesults'

Go Primary key and FOREIGN KEY constraints for SQL