All in all, there should be a ‘Primary Key’ to be a kind of ‘Index’ to the tuple(row) it belongs to, and we can refer to specific tuple(row) by checking primary key. Primary is unique for its tuple, and it should represent this tuple very well.We call those columns which can be a primary key ‘Candidate Key’. A ‘Key’ also could be a combination of several columns. A row can only have one Primary key, but it can hold many candidate key.
If a tuple have a column name, which is a primary key of another tuple, it will be named as ‘Foreign Key’. Foreign is a reminder saying :“I am a Primary Key somewhere!”
It’s easy to set these keys when establishing tables or we can alter columns after building the table.
add constraint pk_columnName primary key(columnName)
pk_columnName is a name of an entity, for the ‘keys’ will be saved as entities in SQL server. Because there’re other type that will be also saved as entities, we use
Hungarian Notation to add a
pk_ in front of it. Here’s another example of adding a foreign key.
alter table tablename add constraint fk_columnName foreign key(columnName) references TableName(ColumnName)
Some times, we can add custom rules to limit the value of a column.
A trigger is just like a IFTTT , which contains 2 part: Condition and Function. The former decide when the trigger is activated, and the latter decide what the trigger will do when activated.
For the condition one, we have 3 options : delete, alter and insert, all in literal meanings. And the trigger’s format is basically like this:
create trigger triggername on tablename for delete/alter/insert as begin --what the trigger will do (you can declare something, select something and update/delete/insert) end
when you use trigger, actually there’re some tables for you :
altered , which hold the changed data.