framework/db/mssql/Schema.php
private function loadTableConstraints($tableName, $returnType)
    {
        static $sql = <<<'SQL'
SELECT
    [o].[name] AS [name],
    COALESCE([ccol].[name], [dcol].[name], [fccol].[name], [kiccol].[name]) AS [column_name],
    RTRIM([o].[type]) AS [type],
    OBJECT_SCHEMA_NAME([f].[referenced_object_id]) AS [foreign_table_schema],
    OBJECT_NAME([f].[referenced_object_id]) AS [foreign_table_name],
    [ffccol].[name] AS [foreign_column_name],
    [f].[update_referential_action_desc] AS [on_update],
    [f].[delete_referential_action_desc] AS [on_delete],
    [c].[definition] AS [check_expr],
    [d].[definition] AS [default_expr]
FROM (SELECT OBJECT_ID(:fullName) AS [object_id]) AS [t]
INNER JOIN [sys].[objects] AS [o]
    ON [o].[parent_object_id] = [t].[object_id] AND [o].[type] IN ('PK', 'UQ', 'C', 'D', 'F')
LEFT JOIN [sys].[check_constraints] AS [c]
    ON [c].[object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [ccol]
    ON [ccol].[object_id] = [c].[parent_object_id] AND [ccol].[column_id] = [c].[parent_column_id]
LEFT JOIN [sys].[default_constraints] AS [d]
    ON [d].[object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [dcol]
    ON [dcol].[object_id] = [d].[parent_object_id] AND [dcol].[column_id] = [d].[parent_column_id]
LEFT JOIN [sys].[key_constraints] AS [k]
    ON [k].[object_id] = [o].[object_id]
LEFT JOIN [sys].[index_columns] AS [kic]
    ON [kic].[object_id] = [k].[parent_object_id] AND [kic].[index_id] = [k].[unique_index_id]
LEFT JOIN [sys].[columns] AS [kiccol]
    ON [kiccol].[object_id] = [kic].[object_id] AND [kiccol].[column_id] = [kic].[column_id]
LEFT JOIN [sys].[foreign_keys] AS [f]
    ON [f].[object_id] = [o].[object_id]
LEFT JOIN [sys].[foreign_key_columns] AS [fc]
    ON [fc].[constraint_object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [fccol]
    ON [fccol].[object_id] = [fc].[parent_object_id] AND [fccol].[column_id] = [fc].[parent_column_id]
LEFT JOIN [sys].[columns] AS [ffccol]
    ON [ffccol].[object_id] = [fc].[referenced_object_id] AND [ffccol].[column_id] = [fc].[referenced_column_id]
ORDER BY [kic].[key_ordinal] ASC, [fc].[constraint_column_id] ASC
SQL;

        $resolvedName = $this->resolveTableName($tableName);
        $constraints = $this->db->createCommand($sql, [
            ':fullName' => $resolvedName->fullName,
        ])->queryAll();
        $constraints = $this->normalizePdoRowKeyCase($constraints, true);
        $constraints = ArrayHelper::index($constraints, null, ['type', 'name']);
        $result = [
            'primaryKey' => null,
            'foreignKeys' => [],
            'uniques' => [],
            'checks' => [],
            'defaults' => [],
        ];
        foreach ($constraints as $type => $names) {
            foreach ($names as $name => $constraint) {
                switch ($type) {
                    case 'PK':
                        $result['primaryKey'] = new Constraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                        ]);
                        break;
                    case 'F':
                        $result['foreignKeys'][] = new ForeignKeyConstraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                            'foreignSchemaName' => $constraint[0]['foreign_table_schema'],
                            'foreignTableName' => $constraint[0]['foreign_table_name'],
                            'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'),
                            'onDelete' => str_replace('_', '', $constraint[0]['on_delete']),
                            'onUpdate' => str_replace('_', '', $constraint[0]['on_update']),
                        ]);
                        break;
                    case 'UQ':
                        $result['uniques'][] = new Constraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                        ]);
                        break;
                    case 'C':
                        $result['checks'][] = new CheckConstraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                            'expression' => $constraint[0]['check_expr'],
                        ]);
                        break;
                    case 'D':
                        $result['defaults'][] = new DefaultValueConstraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                            'value' => $constraint[0]['default_expr'],
                        ]);
                        break;
                }
            }
        }
        foreach ($result as $type => $data) {
            $this->setTableMetadata($tableName, $type, $data);
        }

        return $result[$returnType];
    }
private function loadTableConstraints($tableName, $returnType)
    {
        static $sql = <<<'SQL'
SELECT
    [o].[name] AS [name],
    COALESCE([ccol].[name], [dcol].[name], [fccol].[name], [kiccol].[name]) AS [column_name],
    RTRIM([o].[type]) AS [type],
    OBJECT_SCHEMA_NAME([f].[referenced_object_id]) AS [foreign_table_schema],
    OBJECT_NAME([f].[referenced_object_id]) AS [foreign_table_name],
    [ffccol].[name] AS [foreign_column_name],
    [f].[update_referential_action_desc] AS [on_update],
    [f].[delete_referential_action_desc] AS [on_delete],
    [c].[definition] AS [check_expr],
    [d].[definition] AS [default_expr]
FROM (SELECT OBJECT_ID(:fullName) AS [object_id]) AS [t]
INNER JOIN [sys].[objects] AS [o]
    ON [o].[parent_object_id] = [t].[object_id] AND [o].[type] IN ('PK', 'UQ', 'C', 'D', 'F')
LEFT JOIN [sys].[check_constraints] AS [c]
    ON [c].[object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [ccol]
    ON [ccol].[object_id] = [c].[parent_object_id] AND [ccol].[column_id] = [c].[parent_column_id]
LEFT JOIN [sys].[default_constraints] AS [d]
    ON [d].[object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [dcol]
    ON [dcol].[object_id] = [d].[parent_object_id] AND [dcol].[column_id] = [d].[parent_column_id]
LEFT JOIN [sys].[key_constraints] AS [k]
    ON [k].[object_id] = [o].[object_id]
LEFT JOIN [sys].[index_columns] AS [kic]
    ON [kic].[object_id] = [k].[parent_object_id] AND [kic].[index_id] = [k].[unique_index_id]
LEFT JOIN [sys].[columns] AS [kiccol]
    ON [kiccol].[object_id] = [kic].[object_id] AND [kiccol].[column_id] = [kic].[column_id]
LEFT JOIN [sys].[foreign_keys] AS [f]
    ON [f].[object_id] = [o].[object_id]
LEFT JOIN [sys].[foreign_key_columns] AS [fc]
    ON [fc].[constraint_object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [fccol]
    ON [fccol].[object_id] = [fc].[parent_object_id] AND [fccol].[column_id] = [fc].[parent_column_id]
LEFT JOIN [sys].[columns] AS [ffccol]
    ON [ffccol].[object_id] = [fc].[referenced_object_id] AND [ffccol].[column_id] = [fc].[referenced_column_id]
ORDER BY [kic].[key_ordinal] ASC, [fc].[constraint_column_id] ASC
SQL;

        $resolvedName = $this->resolveTableName($tableName);
        $constraints = $this->db->createCommand($sql, [
            ':fullName' => $resolvedName->fullName,
        ])->queryAll();
        $constraints = $this->normalizePdoRowKeyCase($constraints, true);
        $constraints = ArrayHelper::index($constraints, null, ['type', 'name']);
        $result = [
            'primaryKey' => null,
            'foreignKeys' => [],
            'uniques' => [],
            'checks' => [],
            'defaults' => [],
        ];
        foreach ($constraints as $type => $names) {
            foreach ($names as $name => $constraint) {
                switch ($type) {
                    case 'PK':
                        $result['primaryKey'] = new Constraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                        ]);
                        break;
                    case 'F':
                        $result['foreignKeys'][] = new ForeignKeyConstraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                            'foreignSchemaName' => $constraint[0]['foreign_table_schema'],
                            'foreignTableName' => $constraint[0]['foreign_table_name'],
                            'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'),
                            'onDelete' => str_replace('_', '', $constraint[0]['on_delete']),
                            'onUpdate' => str_replace('_', '', $constraint[0]['on_update']),
                        ]);
                        break;
                    case 'UQ':
                        $result['uniques'][] = new Constraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                        ]);
                        break;
                    case 'C':
                        $result['checks'][] = new CheckConstraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                            'expression' => $constraint[0]['check_expr'],
                        ]);
                        break;
                    case 'D':
                        $result['defaults'][] = new DefaultValueConstraint([
                            'name' => $name,
                            'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'),
                            'value' => $constraint[0]['default_expr'],
                        ]);
                        break;
                }
            }
        }
        foreach ($result as $type => $data) {
            $this->setTableMetadata($tableName, $type, $data);
        }

        return $result[$returnType];
    }
private function loadTableConstraints ( $ tableName , $ returnType ) { static $ sql =  <<<'SQL'
SELECT
    [o].[name] AS [name],
    COALESCE([ccol].[name], [dcol].[name], [fccol].[name], [kiccol].[name]) AS [column_name],
    RTRIM([o].[type]) AS [type],
    OBJECT_SCHEMA_NAME([f].[referenced_object_id]) AS [foreign_table_schema],
    OBJECT_NAME([f].[referenced_object_id]) AS [foreign_table_name],
    [ffccol].[name] AS [foreign_column_name],
    [f].[update_referential_action_desc] AS [on_update],
    [f].[delete_referential_action_desc] AS [on_delete],
    [c].[definition] AS [check_expr],
    [d].[definition] AS [default_expr]
FROM (SELECT OBJECT_ID(:fullName) AS [object_id]) AS [t]
INNER JOIN [sys].[objects] AS [o]
    ON [o].[parent_object_id] = [t].[object_id] AND [o].[type] IN ('PK', 'UQ', 'C', 'D', 'F')
LEFT JOIN [sys].[check_constraints] AS [c]
    ON [c].[object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [ccol]
    ON [ccol].[object_id] = [c].[parent_object_id] AND [ccol].[column_id] = [c].[parent_column_id]
LEFT JOIN [sys].[default_constraints] AS [d]
    ON [d].[object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [dcol]
    ON [dcol].[object_id] = [d].[parent_object_id] AND [dcol].[column_id] = [d].[parent_column_id]
LEFT JOIN [sys].[key_constraints] AS [k]
    ON [k].[object_id] = [o].[object_id]
LEFT JOIN [sys].[index_columns] AS [kic]
    ON [kic].[object_id] = [k].[parent_object_id] AND [kic].[index_id] = [k].[unique_index_id]
LEFT JOIN [sys].[columns] AS [kiccol]
    ON [kiccol].[object_id] = [kic].[object_id] AND [kiccol].[column_id] = [kic].[column_id]
LEFT JOIN [sys].[foreign_keys] AS [f]
    ON [f].[object_id] = [o].[object_id]
LEFT JOIN [sys].[foreign_key_columns] AS [fc]
    ON [fc].[constraint_object_id] = [o].[object_id]
LEFT JOIN [sys].[columns] AS [fccol]
    ON [fccol].[object_id] = [fc].[parent_object_id] AND [fccol].[column_id] = [fc].[parent_column_id]
LEFT JOIN [sys].[columns] AS [ffccol]
    ON [ffccol].[object_id] = [fc].[referenced_object_id] AND [ffccol].[column_id] = [fc].[referenced_column_id]
ORDER BY [kic].[key_ordinal] ASC, [fc].[constraint_column_id] ASC
SQL ; $ resolvedName = $ this -> resolveTableName ( $ tableName ) ; $ constraints = $ this -> db -> createCommand ( $ sql , [ ':fullName' => $ resolvedName -> fullName , ] ) -> queryAll ( ) ; $ constraints = $ this -> normalizePdoRowKeyCase ( $ constraints , true ) ; $ constraints = ArrayHelper :: index ( $ constraints , null , [ 'type' , 'name' ] ) ; $ result = [ 'primaryKey' => null , 'foreignKeys' => [ ] , 'uniques' => [ ] , 'checks' => [ ] , 'defaults' => [ ] , ] ; foreach ( $ constraints as $ type => $ names ) { foreach ( $ names as $ name => $ constraint ) { switch ( $ type ) { case 'PK' : $ result [ 'primaryKey' ] = new Constraint ( [ 'name' => $ name , 'columnNames' => ArrayHelper :: getColumn ( $ constraint , 'column_name' ) , ] ) ; break ; case 'F' : $ result [ 'foreignKeys' ] [ ] = new ForeignKeyConstraint ( [ 'name' => $ name , 'columnNames' => ArrayHelper :: getColumn ( $ constraint , 'column_name' ) , 'foreignSchemaName' => $ constraint [ 0 ] [ 'foreign_table_schema' ] , 'foreignTableName' => $ constraint [ 0 ] [ 'foreign_table_name' ] , 'foreignColumnNames' => ArrayHelper :: getColumn ( $ constraint , 'foreign_column_name' ) , 'onDelete' => str_replace ( '_' , '' , $ constraint [ 0 ] [ 'on_delete' ] ) , 'onUpdate' => str_replace ( '_' , '' , $ constraint [ 0 ] [ 'on_update' ] ) , ] ) ; break ; case 'UQ' : $ result [ 'uniques' ] [ ] = new Constraint ( [ 'name' => $ name , 'columnNames' => ArrayHelper :: getColumn ( $ constraint , 'column_name' ) , ] ) ; break ; case 'C' : $ result [ 'checks' ] [ ] = new CheckConstraint ( [ 'name' => $ name , 'columnNames' => ArrayHelper :: getColumn ( $ constraint , 'column_name' ) , 'expression' => $ constraint [ 0 ] [ 'check_expr' ] , ] ) ; break ; case 'D' : $ result [ 'defaults' ] [ ] = new DefaultValueConstraint ( [ 'name' => $ name , 'columnNames' => ArrayHelper :: getColumn ( $ constraint , 'column_name' ) , 'value' => $ constraint [ 0 ] [ 'default_expr' ] , ] ) ; break ; } } } foreach ( $ result as $ type => $ data ) { $ this -> setTableMetadata ( $ tableName , $ type , $ data ) ; } return $ result [ $ returnType ] ; }
Loads multiple types of constraints and returns the specified ones.
@param string $tableName table name.
@param string $returnType return type:
- primaryKey
- foreignKeys
- uniques
- checks
- defaults
@return mixed constraints.
Loads multiple types of constraints and returns the specified ones .