Azure Database Grammar

• 建立外部索引鍵關聯性

Link_table_PK_ID int,
CONSTRAINT FK_Name FOREIGN KEY (Link_table_PK_ID) REFERENCES table_name (Link_table_PK_ID) ON DELETE CASCADE ON UPDATE CASCADE

ex:
CREATE TABLE Device (
    PK_deviceID bigint NOT NULL PRIMARY KEY IDENTITY(1,1),
    SN varchar(32),
    PK_nkgID int, 
    CONSTRAINT FK_nkgNameID FOREIGN KEY (PK_nkgID) REFERENCES nNkgName (PK_nkgID) ON DELETE CASCADE ON UPDATE CASCADE
);

ref: 建立外部索引鍵關聯性

• Insert Data (include foreign key)

1. INSERT INTO table_name([column_name]) values ('string')

2. insert into table_name ([column_1], [FK_ID]) values ('string', (select FK_ID from FK_table_name where FK_table_name_condition));

ex:
1. INSERT INTO xxx([Names]) values ('124test')

2. insert into Device ([SN], [PK_ID]) values ('sn1234', (select PK_ID from Test where PK_ID = 1));

• Modify Column Name

EXEC sp_rename 'database_name.tabale_name.column_old_name', 'column_new_name', 'COLUMN';

ex:
EXEC sp_rename 'xxx.Device.sn', 'SN', 'COLUMN'

• Delete Data

DELETE FROM table_name WHERE the_condition;

ex:
DELETE FROM nNKGName WHERE PK_NKGID >= 0;

• Reset Count

DBCC CHECKIDENT (table_name, RESEED, the_int_value);
  
ex:
DBCC CHECKIDENT (xxx, RESEED, 0);

• Delete Table

DROP TABLE table_name;