~ubuntu-branches/debian/jessie/sqlalchemy/jessie : revision 35

1

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

2

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

3

4

5

<head>

6

7

8

<title>

9

10

11

Defining Constraints and Indexes

12

—

13

SQLAlchemy 0.8 Documentation

14

15

</title>

16

17

18

19

20

21

var DOCUMENTATION_OPTIONS = {

22

URL_ROOT: '../',

23

VERSION: '0.8.3',

24

COLLAPSE_MODINDEX: false,

25

FILE_SUFFIX: '.html'

26

};

27

</script>

28

29

30

31

32

33

34

35

36

37

38

39

40

</head>

41

<body>

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

<h1>SQLAlchemy 0.8 Documentation</h1>

59

60

61

Search:

62

63

64

65

66

</form>

67

</div>

68

69

70

Release: 0.8.3 | Release Date: October 26, 2013

71

72

73

</div>

74

75

</div>

76

77

78

79

<ul>

80

<li>Prev:

81

<a href="defaults.html" title="previous chapter">Column Insert/Update Defaults</a>

82

</li>

83

<li>Next:

84

<a href="ddl.html" title="next chapter">Customizing DDL</a>

85

</li>

86

87

<li>

88

<a href="../contents.html">Table of Contents</a> |

89

<a href="../genindex.html">Index</a>

90

| <a href="../_sources/core/constraints.txt">view source

91

</li>

92

</ul>

93

</div>

94

95

96

<a href="../index.html">SQLAlchemy 0.8 Documentation</a>

97

» <a href="index.html" title="SQLAlchemy Core">SQLAlchemy Core</a>

98

» <a href="schema.html" title="Schema Definition Language">Schema Definition Language</a>

99

»

100

Defining Constraints and Indexes

101

102

103

<h2>

104

105

Defining Constraints and Indexes

106

107

</h2>

108

</div>

109

110

</div>

111

112

113

114

115

<h3><a href="../index.html">Table of Contents</a></h3>

116

<ul>

117

<li><a class="reference internal" href="#">Defining Constraints and Indexes</a><ul>

118

<li><a class="reference internal" href="#defining-foreign-keys">Defining Foreign Keys</a><ul>

119

<li><a class="reference internal" href="#creating-dropping-foreign-key-constraints-via-alter">Creating/Dropping Foreign Key Constraints via ALTER</a></li>

120

<li><a class="reference internal" href="#on-update-and-on-delete">ON UPDATE and ON DELETE</a></li>

121

</ul>

122

</li>

123

<li><a class="reference internal" href="#unique-constraint">UNIQUE Constraint</a></li>

124

<li><a class="reference internal" href="#check-constraint">CHECK Constraint</a></li>

125

<li><a class="reference internal" href="#setting-up-constraints-when-using-the-declarative-orm-extension">Setting up Constraints when using the Declarative ORM Extension</a></li>

126

<li><a class="reference internal" href="#constraints-api">Constraints API</a></li>

127

<li><a class="reference internal" href="#indexes">Indexes</a><ul>

128

<li><a class="reference internal" href="#functional-indexes">Functional Indexes</a></li>

129

</ul>

130

</li>

131

<li><a class="reference internal" href="#index-api">Index API</a></li>

132

</ul>

133

</li>

134

</ul>

135

136

137

<h4>Previous Topic</h4>

138

139

<a href="defaults.html" title="previous chapter">Column Insert/Update Defaults</a>

140

141

<h4>Next Topic</h4>

142

143

<a href="ddl.html" title="next chapter">Customizing DDL</a>

144

145

146

147

<h4>Quick Search</h4>

148

149

150

151

152

153

</form>

154

155

156

</div>

157

158

159

160

161

<h1>Defining Constraints and Indexes<a class="headerlink" href="#defining-constraints-and-indexes" title="Permalink to this headline">¶</a></h1>

162

This section will discuss SQL constraints and indexes. In SQLAlchemy

163

the key classes include <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> and <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>.

164

165

<h2>Defining Foreign Keys<a class="headerlink" href="#defining-foreign-keys" title="Permalink to this headline">¶</a></h2>

166

A foreign key in SQL is a table-level construct that constrains one or more

167

columns in that table to only allow values that are present in a different set

168

of columns, typically but not always located on a different table. We call the

169

columns which are constrained the foreign key columns and the columns which

170

they are constrained towards the referenced columns. The referenced columns

171

almost always define the primary key for their owning table, though there are

172

exceptions to this. The foreign key is the “joint” that connects together

173

pairs of rows which have a relationship with each other, and SQLAlchemy

174

assigns very deep importance to this concept in virtually every area of its

175

operation.

176

In SQLAlchemy as well as in DDL, foreign key constraints can be defined as

177

additional attributes within the table clause, or for single-column foreign

178

keys they may optionally be specified within the definition of a single

179

column. The single column foreign key is more common, and at the column level

180

is specified by constructing a <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object

181

as an argument to a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object:

182

<div class="highlight-python"><div class="highlight"><pre>user_preference = Table('user_preference', metadata,

183

Column('pref_id', Integer, primary_key=True),

184

Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),

185

Column('pref_name', String(40), nullable=False),

186

Column('pref_value', String(100))

187

)</pre></div>

188

</div>

189

Above, we define a new table <tt class="docutils literal">user_preference</tt> for which each row must

190

contain a value in the <tt class="docutils literal">user_id</tt> column that also exists in the <tt class="docutils literal">user</tt>

191

table’s <tt class="docutils literal">user_id</tt> column.

192

The argument to <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> is most commonly a

193

string of the form <tablename>.<columnname>, or for a table in a remote

194

schema or “owner” of the form <schemaname>.<tablename>.<columnname>. It may

195

also be an actual <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object, which as we’ll

196

see later is accessed from an existing <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

197

object via its <tt class="docutils literal">c</tt> collection:

198

<div class="highlight-python"><div class="highlight"><pre>ForeignKey(user.c.user_id)</pre></div>

199

</div>

200

The advantage to using a string is that the in-python linkage between <tt class="docutils literal">user</tt>

201

and <tt class="docutils literal">user_preference</tt> is resolved only when first needed, so that table

202

objects can be easily spread across multiple modules and defined in any order.

203

Foreign keys may also be defined at the table level, using the

204

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> object. This object can

205

describe a single- or multi-column foreign key. A multi-column foreign key is

206

known as a composite foreign key, and almost always references a table that

207

has a composite primary key. Below we define a table <tt class="docutils literal">invoice</tt> which has a

208

composite primary key:

209

<div class="highlight-python"><div class="highlight"><pre>invoice = Table('invoice', metadata,

210

Column('invoice_id', Integer, primary_key=True),

211

Column('ref_num', Integer, primary_key=True),

212

Column('description', String(60), nullable=False)

213

)</pre></div>

214

</div>

215

And then a table <tt class="docutils literal">invoice_item</tt> with a composite foreign key referencing

216

<tt class="docutils literal">invoice</tt>:

217

<div class="highlight-python"><div class="highlight"><pre>invoice_item = Table('invoice_item', metadata,

218

Column('item_id', Integer, primary_key=True),

219

Column('item_name', String(60), nullable=False),

220

Column('invoice_id', Integer, nullable=False),

221

Column('ref_num', Integer, nullable=False),

222

ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', 'invoice.ref_num'])

223

)</pre></div>

224

</div>

225

It’s important to note that the

226

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> is the only way to define a

227

composite foreign key. While we could also have placed individual

228

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> objects on both the

229

<tt class="docutils literal">invoice_item.invoice_id</tt> and <tt class="docutils literal">invoice_item.ref_num</tt> columns, SQLAlchemy

230

would not be aware that these two values should be paired together - it would

231

be two individual foreign key constraints instead of a single composite

232

foreign key referencing two columns.

233

234

<h3>Creating/Dropping Foreign Key Constraints via ALTER<a class="headerlink" href="#creating-dropping-foreign-key-constraints-via-alter" title="Permalink to this headline">¶</a></h3>

235

In all the above examples, the <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object

236

causes the “REFERENCES” keyword to be added inline to a column definition

237

within a “CREATE TABLE” statement when

238

<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal">create_all()</tt></a> is issued, and

239

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> invokes the “CONSTRAINT”

240

keyword inline with “CREATE TABLE”. There are some cases where this is

241

undesireable, particularly when two tables reference each other mutually, each

242

with a foreign key referencing the other. In such a situation at least one of

243

the foreign key constraints must be generated after both tables have been

244

built. To support such a scheme, <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> and

245

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> offer the flag

246

<tt class="docutils literal">use_alter=True</tt>. When using this flag, the constraint will be generated

247

using a definition similar to “ALTER TABLE <tablename> ADD CONSTRAINT <name>

248

...”. Since a name is required, the <tt class="docutils literal">name</tt> attribute must also be specified.

249

For example:

250

<div class="highlight-python"><div class="highlight"><pre>node = Table('node', meta,

251

Column('node_id', Integer, primary_key=True),

252

Column('primary_element', Integer,

253

ForeignKey('element.element_id', use_alter=True, name='fk_node_element_id')

254

)

255

)

256

257

element = Table('element', meta,

258

Column('element_id', Integer, primary_key=True),

259

Column('parent_node_id', Integer),

260

ForeignKeyConstraint(

261

['parent_node_id'],

262

['node.node_id'],

263

use_alter=True,

264

name='fk_element_parent_node_id'

265

)

266

)</pre></div>

267

</div>

268

</div>

269

270

<h3>ON UPDATE and ON DELETE<a class="headerlink" href="#on-update-and-on-delete" title="Permalink to this headline">¶</a></h3>

271

Most databases support cascading of foreign key values, that is the when a

272

parent row is updated the new value is placed in child rows, or when the

273

parent row is deleted all corresponding child rows are set to null or deleted.

274

In data definition language these are specified using phrases like “ON UPDATE

275

CASCADE”, “ON DELETE CASCADE”, and “ON DELETE SET NULL”, corresponding to

276

foreign key constraints. The phrase after “ON UPDATE” or “ON DELETE” may also

277

other allow other phrases that are specific to the database in use. The

278

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> and

279

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> objects support the

280

generation of this clause via the <tt class="docutils literal">onupdate</tt> and <tt class="docutils literal">ondelete</tt> keyword

281

arguments. The value is any string which will be output after the appropriate

282

“ON UPDATE” or “ON DELETE” phrase:

283

<div class="highlight-python"><div class="highlight"><pre>child = Table('child', meta,

284

Column('id', Integer,

285

ForeignKey('parent.id', onupdate="CASCADE", ondelete="CASCADE"),

286

primary_key=True

287

)

288

)

289

290

composite = Table('composite', meta,

291

Column('id', Integer, primary_key=True),

292

Column('rev_id', Integer),

293

Column('note_id', Integer),

294

ForeignKeyConstraint(

295

['rev_id', 'note_id'],

296

['revisions.id', 'revisions.note_id'],

297

onupdate="CASCADE", ondelete="SET NULL"

298

)

299

)</pre></div>

300

</div>

301

Note that these clauses are not supported on SQLite, and require <tt class="docutils literal">InnoDB</tt>

302

tables when used with MySQL. They may also not be supported on other

303

databases.

304

</div>

305

</div>

306

307

<h2>UNIQUE Constraint<a class="headerlink" href="#unique-constraint" title="Permalink to this headline">¶</a></h2>

308

Unique constraints can be created anonymously on a single column using the

309

<tt class="docutils literal">unique</tt> keyword on <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>. Explicitly named

310

unique constraints and/or those with multiple columns are created via the

311

<a class="reference internal" href="#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><tt class="xref py py-class docutils literal">UniqueConstraint</tt></a> table-level construct.

312

<div class="highlight-python+sql"><div class="highlight"><pre>meta = MetaData()

313

mytable = Table('mytable', meta,

314

315

# per-column anonymous unique constraint

316

Column('col1', Integer, unique=True),

317

318

Column('col2', Integer),

319

Column('col3', Integer),

320

321

# explicit/composite unique constraint. 'name' is optional.

322

UniqueConstraint('col2', 'col3', name='uix_1')

323

)</pre></div>

324

</div>

325

</div>

326

327

<h2>CHECK Constraint<a class="headerlink" href="#check-constraint" title="Permalink to this headline">¶</a></h2>

328

Check constraints can be named or unnamed and can be created at the Column or

329

Table level, using the <a class="reference internal" href="#sqlalchemy.schema.CheckConstraint" title="sqlalchemy.schema.CheckConstraint"><tt class="xref py py-class docutils literal">CheckConstraint</tt></a> construct.

330

The text of the check constraint is passed directly through to the database,

331

so there is limited “database independent” behavior. Column level check

332

constraints generally should only refer to the column to which they are

333

placed, while table level constraints can refer to any columns in the table.

334

Note that some databases do not actively support check constraints such as

335

MySQL.

336

<div class="highlight-python+sql"><div class="highlight"><pre>meta = MetaData()

337

mytable = Table('mytable', meta,

338

339

# per-column CHECK constraint

340

Column('col1', Integer, CheckConstraint('col1>5')),

341

342

Column('col2', Integer),

343

Column('col3', Integer),

344

345

# table level CHECK constraint. 'name' is optional.

346

CheckConstraint('col2 > col3 + 5', name='check1')

347

)

348

349

<a href='#' class='sql_link'>sql</a>mytable.create(engine)

350

<div class='popup_sql'>CREATE TABLE mytable (

351

col1 INTEGER CHECK (col1>5),

352

col2 INTEGER,

353

col3 INTEGER,

354

CONSTRAINT check1 CHECK (col2 > col3 + 5)

355

)</div></pre></div>

356

</div>

357

</div>

358

359

<h2>Setting up Constraints when using the Declarative ORM Extension<a class="headerlink" href="#setting-up-constraints-when-using-the-declarative-orm-extension" title="Permalink to this headline">¶</a></h2>

360

The <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is the SQLAlchemy Core construct that allows one to define

361

table metadata, which among other things can be used by the SQLAlchemy ORM

362

as a target to map a class. The <a class="reference internal" href="../orm/extensions/declarative.html">Declarative</a>

363

extension allows the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object to be created automatically, given

364

the contents of the table primarily as a mapping of <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> objects.

365

To apply table-level constraint objects such as <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a>

366

to a table defined using Declarative, use the <tt class="docutils literal">__table_args__</tt> attribute,

367

described at <a class="reference internal" href="../orm/extensions/declarative.html#declarative-table-args">Table Configuration</a>.

368

</div>

369

370

<h2>Constraints API<a class="headerlink" href="#constraints-api" title="Permalink to this headline">¶</a></h2>

371

372

373

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">Constraint</tt><big>(</big>name=None, deferrable=None, initially=None, _create_rule=None, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Constraint" title="Permalink to this definition">¶</a></dt>

374

<dd>Bases: <a class="reference internal" href="metadata.html#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

375

A table-level SQL constraint.

376

</dd></dl>

377

378

379

380

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">CheckConstraint</tt><big>(</big>sqltext, name=None, deferrable=None, initially=None, table=None, _create_rule=None, _autoattach=True<big>)</big><a class="headerlink" href="#sqlalchemy.schema.CheckConstraint" title="Permalink to this definition">¶</a></dt>

381

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.Constraint</tt></a>

382

A table- or column-level CHECK constraint.

383

Can be included in the definition of a Table or Column.

384

</dd></dl>

385

386

387

388

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ColumnCollectionConstraint</tt><big>(</big>*columns, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ColumnCollectionConstraint" title="Permalink to this definition">¶</a></dt>

389

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionMixin</tt>, <a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.Constraint</tt></a>

390

A constraint that proxies a ColumnCollection.

391

</dd></dl>

392

393

394

395

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ForeignKey</tt><big>(</big>column, _constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, schema=None, initially=None, link_to_name=False, match=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey" title="Permalink to this definition">¶</a></dt>

396

<dd>Bases: <a class="reference internal" href="metadata.html#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

397

Defines a dependency between two columns.

398

<tt class="docutils literal">ForeignKey</tt> is specified as an argument to a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object,

399

e.g.:

400

<div class="highlight-python"><div class="highlight"><pre>t = Table("remote_table", metadata,

401

Column("remote_id", ForeignKey("main_table.id"))

402

)</pre></div>

403

</div>

404

Note that <tt class="docutils literal">ForeignKey</tt> is only a marker object that defines

405

a dependency between two columns. The actual constraint

406

is in all cases represented by the <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a>

407

object. This object will be generated automatically when

408

a <tt class="docutils literal">ForeignKey</tt> is associated with a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> which

409

in turn is associated with a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. Conversely,

410

when <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> is applied to a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

411

<tt class="docutils literal">ForeignKey</tt> markers are automatically generated to be

412

present on each associated <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, which are also

413

associated with the constraint object.

414

Note that you cannot define a “composite” foreign key constraint,

415

that is a constraint between a grouping of multiple parent/child

416

columns, using <tt class="docutils literal">ForeignKey</tt> objects. To define this grouping,

417

the <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> object must be used, and applied

418

to the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>. The associated <tt class="docutils literal">ForeignKey</tt> objects

419

are created automatically.

420

The <tt class="docutils literal">ForeignKey</tt> objects associated with an individual

421

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object are available in the <cite>foreign_keys</cite> collection

422

of that column.

423

Further examples of foreign key configuration are in

424

metadata_foreignkeys.

425

426

427

<tt class="descname">__init__</tt><big>(</big>column, _constraint=None, use_alter=False, name=None, onupdate=None, ondelete=None, deferrable=None, schema=None, initially=None, link_to_name=False, match=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.__init__" title="Permalink to this definition">¶</a></dt>

428

<dd>Construct a column-level FOREIGN KEY.

429

The <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object when constructed generates a

430

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> which is associated with the parent

431

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> object’s collection of constraints.

432

433

434

435

436

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

437

<li>column – A single target column for the key relationship. A

438

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> object or a column name as a string:

439

<tt class="docutils literal">tablename.columnkey</tt> or <tt class="docutils literal">schema.tablename.columnkey</tt>.

440

<tt class="docutils literal">columnkey</tt> is the <tt class="docutils literal">key</tt> which has been assigned to the column

441

(defaults to the column name itself), unless <tt class="docutils literal">link_to_name</tt> is

442

<tt class="docutils literal">True</tt> in which case the rendered name of the column is used.

443

444

New in version 0.7.4: Note that if the schema name is not included, and the

445

underlying <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> has a “schema”, that value will

446

be used.

447

</div>

448

</li>

449

<li>name – Optional string. An in-database name for the key if

450

<cite>constraint</cite> is not provided.</li>

451

<li>onupdate – Optional string. If set, emit ON UPDATE <value> when

452

issuing DDL for this constraint. Typical values include CASCADE,

453

DELETE and RESTRICT.</li>

454

<li>ondelete – Optional string. If set, emit ON DELETE <value> when

455

issuing DDL for this constraint. Typical values include CASCADE,

456

DELETE and RESTRICT.</li>

457

<li>deferrable – Optional bool. If set, emit DEFERRABLE or NOT

458

DEFERRABLE when issuing DDL for this constraint.</li>

459

<li>initially – Optional string. If set, emit INITIALLY <value> when

460

issuing DDL for this constraint.</li>

461

<li>link_to_name – if True, the string name given in <tt class="docutils literal">column</tt> is

462

the rendered name of the referenced column, not its locally

463

assigned <tt class="docutils literal">key</tt>.</li>

464

<li>use_alter – passed to the underlying

465

<a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a> to indicate the constraint should be

466

generated/dropped externally from the CREATE TABLE/ DROP TABLE

467

statement. See that classes’ constructor for details.</li>

468

<li>match – Optional string. If set, emit MATCH <value> when issuing

469

DDL for this constraint. Typical values include SIMPLE, PARTIAL

470

and FULL.</li>

471

<li>schema – Deprecated; this flag does nothing and will be removed

472

in 0.9.</li>

473

</ul>

474

</td>

475

</tr>

476

</tbody>

477

</table>

478

</dd></dl>

479

480

481

482

<tt class="descname">column</tt><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.column" title="Permalink to this definition">¶</a></dt>

483

<dd>Return the target <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> referenced by this

484

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

485

If this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> was created using a

486

string-based target column specification, this

487

attribute will on first access initiate a resolution

488

process to locate the referenced remote

489

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>. The resolution process traverses

490

to the parent <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, and

491

<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a> to proceed - if any of these aren’t

492

yet present, an error is raised.

493

</dd></dl>

494

495

496

497

<tt class="descname">copy</tt><big>(</big>schema=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.copy" title="Permalink to this definition">¶</a></dt>

498

<dd>Produce a copy of this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> object.

499

The new <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> will not be bound

500

to any <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>.

501

This method is usually used by the internal

502

copy procedures of <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>,

503

and <a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal">MetaData</tt></a>.

504

505

506

507

508

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body">schema – The returned <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> will

509

reference the original table and column name, qualified

510

by the given string schema name.</td>

511

</tr>

512

</tbody>

513

</table>

514

</dd></dl>

515

516

517

518

<tt class="descname">get_referent</tt><big>(</big>table<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.get_referent" title="Permalink to this definition">¶</a></dt>

519

<dd>Return the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> in the given <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

520

referenced by this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

521

Returns None if this <a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> does not reference the given

522

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>.

523

</dd></dl>

524

525

526

527

<tt class="descname">references</tt><big>(</big>table<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.references" title="Permalink to this definition">¶</a></dt>

528

<dd>Return True if the given <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> is referenced by this

529

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

530

</dd></dl>

531

532

533

534

<tt class="descname">target_fullname</tt><a class="headerlink" href="#sqlalchemy.schema.ForeignKey.target_fullname" title="Permalink to this definition">¶</a></dt>

535

<dd>Return a string based ‘column specification’ for this

536

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a>.

537

This is usually the equivalent of the string-based “tablename.colname”

538

argument first passed to the object’s constructor.

539

</dd></dl>

540

541

</dd></dl>

542

543

544

545

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">ForeignKeyConstraint</tt><big>(</big>columns, refcolumns, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, use_alter=False, link_to_name=False, match=None, table=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKeyConstraint" title="Permalink to this definition">¶</a></dt>

546

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.Constraint" title="sqlalchemy.schema.Constraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.Constraint</tt></a>

547

A table-level FOREIGN KEY constraint.

548

Defines a single column or composite FOREIGN KEY ... REFERENCES

549

constraint. For a no-frills, single column foreign key, adding a

550

<a class="reference internal" href="#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal">ForeignKey</tt></a> to the definition of a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> is a shorthand

551

equivalent for an unnamed, single column <a class="reference internal" href="#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal">ForeignKeyConstraint</tt></a>.

552

Examples of foreign key configuration are in metadata_foreignkeys.

553

554

555

<tt class="descname">__init__</tt><big>(</big>columns, refcolumns, name=None, onupdate=None, ondelete=None, deferrable=None, initially=None, use_alter=False, link_to_name=False, match=None, table=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.ForeignKeyConstraint.__init__" title="Permalink to this definition">¶</a></dt>

556

<dd>Construct a composite-capable FOREIGN KEY.

557

558

559

560

561

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

562

<li>columns – A sequence of local column names. The named columns

563

must be defined and present in the parent Table. The names should

564

match the <tt class="docutils literal">key</tt> given to each column (defaults to the name) unless

565

566

<li>refcolumns – A sequence of foreign column names or Column

567

objects. The columns must all be located within the same Table.</li>

568

<li>name – Optional, the in-database name of the key.</li>

569

<li>onupdate – Optional string. If set, emit ON UPDATE <value> when

570

issuing DDL for this constraint. Typical values include CASCADE,

571

DELETE and RESTRICT.</li>

572

<li>ondelete – Optional string. If set, emit ON DELETE <value> when

573

issuing DDL for this constraint. Typical values include CASCADE,

574

DELETE and RESTRICT.</li>

575

<li>deferrable – Optional bool. If set, emit DEFERRABLE or NOT

576

DEFERRABLE when issuing DDL for this constraint.</li>

577

<li>initially – Optional string. If set, emit INITIALLY <value> when

578

issuing DDL for this constraint.</li>

579

<li>link_to_name – if True, the string name given in <tt class="docutils literal">column</tt> is

580

the rendered name of the referenced column, not its locally assigned

581

582

<li>use_alter – If True, do not emit the DDL for this constraint as

583

part of the CREATE TABLE definition. Instead, generate it via an

584

ALTER TABLE statement issued after the full collection of tables

585

have been created, and drop it via an ALTER TABLE statement before

586

the full collection of tables are dropped. This is shorthand for the

587

usage of <a class="reference internal" href="ddl.html#sqlalchemy.schema.AddConstraint" title="sqlalchemy.schema.AddConstraint"><tt class="xref py py-class docutils literal">AddConstraint</tt></a> and <a class="reference internal" href="ddl.html#sqlalchemy.schema.DropConstraint" title="sqlalchemy.schema.DropConstraint"><tt class="xref py py-class docutils literal">DropConstraint</tt></a> applied

588

as “after-create” and “before-drop” events on the MetaData object.

589

This is normally used to generate/drop constraints on objects that

590

are mutually dependent on each other.</li>

591

<li>match – Optional string. If set, emit MATCH <value> when issuing

592

DDL for this constraint. Typical values include SIMPLE, PARTIAL

593

and FULL.</li>

594

</ul>

595

</td>

596

</tr>

597

</tbody>

598

</table>

599

</dd></dl>

600

601

</dd></dl>

602

603

604

605

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">PrimaryKeyConstraint</tt><big>(</big>*columns, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.PrimaryKeyConstraint" title="Permalink to this definition">¶</a></dt>

606

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.ColumnCollectionConstraint" title="sqlalchemy.schema.ColumnCollectionConstraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionConstraint</tt></a>

607

A table-level PRIMARY KEY constraint.

608

Defines a single column or composite PRIMARY KEY constraint. For a

609

no-frills primary key, adding <tt class="docutils literal">primary_key=True</tt> to one or more

610

<tt class="docutils literal">Column</tt> definitions is a shorthand equivalent for an unnamed single- or

611

multiple-column PrimaryKeyConstraint.

612

</dd></dl>

613

614

615

616

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">UniqueConstraint</tt><big>(</big>*columns, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.UniqueConstraint" title="Permalink to this definition">¶</a></dt>

617

<dd>Bases: <a class="reference internal" href="#sqlalchemy.schema.ColumnCollectionConstraint" title="sqlalchemy.schema.ColumnCollectionConstraint"><tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionConstraint</tt></a>

618

A table-level UNIQUE constraint.

619

Defines a single column or composite UNIQUE constraint. For a no-frills,

620

single column constraint, adding <tt class="docutils literal">unique=True</tt> to the <tt class="docutils literal">Column</tt>

621

definition is a shorthand equivalent for an unnamed, single column

622

UniqueConstraint.

623

</dd></dl>

624

625

</div>

626

627

<h2>Indexes<a class="headerlink" href="#indexes" title="Permalink to this headline">¶</a></h2>

628

Indexes can be created anonymously (using an auto-generated name <tt class="docutils literal">ix_<column

629

label></tt>) for a single column using the inline <tt class="docutils literal">index</tt> keyword on

630

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>, which also modifies the usage of

631

<tt class="docutils literal">unique</tt> to apply the uniqueness to the index itself, instead of adding a

632

separate UNIQUE constraint. For indexes with specific names or which encompass

633

more than one column, use the <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct,

634

which requires a name.

635

Below we illustrate a <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a> with several

636

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> objects associated. The DDL for “CREATE

637

INDEX” is issued right after the create statements for the table:

638

<div class="highlight-python+sql"><div class="highlight"><pre>meta = MetaData()

639

mytable = Table('mytable', meta,

640

# an indexed column, with index "ix_mytable_col1"

641

Column('col1', Integer, index=True),

642

643

# a uniquely indexed column with index "ix_mytable_col2"

644

Column('col2', Integer, index=True, unique=True),

645

646

Column('col3', Integer),

647

Column('col4', Integer),

648

649

Column('col5', Integer),

650

Column('col6', Integer),

651

)

652

653

# place an index on col3, col4

654

Index('idx_col34', mytable.c.col3, mytable.c.col4)

655

656

# place a unique index on col5, col6

657

Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)

658

659

<a href='#' class='sql_link'>sql</a>mytable.create(engine)

660

<div class='popup_sql'>CREATE TABLE mytable (

661

col1 INTEGER,

662

col2 INTEGER,

663

col3 INTEGER,

664

col4 INTEGER,

665

col5 INTEGER,

666

col6 INTEGER

667

)

668

CREATE INDEX ix_mytable_col1 ON mytable (col1)

669

CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)

670

CREATE UNIQUE INDEX myindex ON mytable (col5, col6)

671

CREATE INDEX idx_col34 ON mytable (col3, col4)</div></pre></div>

672

</div>

673

Note in the example above, the <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct is created

674

externally to the table which it corresponds, using <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a>

675

objects directly. <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> also supports

676

“inline” definition inside the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>, using string names to

677

identify columns:

678

<div class="highlight-python"><div class="highlight"><pre>meta = MetaData()

679

mytable = Table('mytable', meta,

680

Column('col1', Integer),

681

682

Column('col2', Integer),

683

684

Column('col3', Integer),

685

Column('col4', Integer),

686

687

# place an index on col1, col2

688

Index('idx_col12', 'col1', 'col2'),

689

690

# place a unique index on col3, col4

691

Index('idx_col34', 'col3', 'col4', unique=True)

692

)</pre></div>

693

</div>

694

695

New in version 0.7: Support of “inline” definition inside the <a class="reference internal" href="metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal">Table</tt></a>

696

for <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>.

697

</div>

698

The <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> object also supports its own <tt class="docutils literal">create()</tt> method:

699

<div class="highlight-python+sql"><div class="highlight"><pre>i = Index('someindex', mytable.c.col5)

700

<a href='#' class='sql_link'>sql</a>i.create(engine)

701

<div class='popup_sql'>CREATE INDEX someindex ON mytable (col5)</div></pre></div>

702

</div>

703

704

<h3>Functional Indexes<a class="headerlink" href="#functional-indexes" title="Permalink to this headline">¶</a></h3>

705

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> supports SQL and function expressions, as supported by the

706

target backend. To create an index against a column using a descending

707

value, the <a class="reference internal" href="sqlelement.html#sqlalchemy.sql.expression.ColumnElement.desc" title="sqlalchemy.sql.expression.ColumnElement.desc"><tt class="xref py py-meth docutils literal">ColumnElement.desc()</tt></a> modifier may be used:

708

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import Index

709

710

Index('someindex', mytable.c.somecol.desc())</pre></div>

711

</div>

712

Or with a backend that supports functional indexes such as Postgresql,

713

a “case insensitive” index can be created using the <tt class="docutils literal">lower()</tt> function:

714

<div class="highlight-python"><div class="highlight"><pre>from sqlalchemy import func, Index

715

716

Index('someindex', func.lower(mytable.c.somecol))</pre></div>

717

</div>

718

719

New in version 0.8: <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> supports SQL expressions and functions

720

as well as plain columns.

721

</div>

722

</div>

723

</div>

724

725

<h2>Index API<a class="headerlink" href="#index-api" title="Permalink to this headline">¶</a></h2>

726

727

728

class <tt class="descclassname">sqlalchemy.schema.</tt><tt class="descname">Index</tt><big>(</big>name, *expressions, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Index" title="Permalink to this definition">¶</a></dt>

729

<dd>Bases: <tt class="xref py py-class docutils literal">sqlalchemy.schema.ColumnCollectionMixin</tt>, <a class="reference internal" href="metadata.html#sqlalchemy.schema.SchemaItem" title="sqlalchemy.schema.SchemaItem"><tt class="xref py py-class docutils literal">sqlalchemy.schema.SchemaItem</tt></a>

730

A table-level INDEX.

731

Defines a composite (one or more column) INDEX.

732

E.g.:

733

<div class="highlight-python"><div class="highlight"><pre>sometable = Table("sometable", metadata,

734

Column("name", String(50)),

735

Column("address", String(100))

736

)

737

738

Index("some_index", sometable.c.name)</pre></div>

739

</div>

740

For a no-frills, single column index, adding

741

<a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> also supports <tt class="docutils literal">index=True</tt>:

742

<div class="highlight-python"><div class="highlight"><pre>sometable = Table("sometable", metadata,

743

Column("name", String(50), index=True)

744

)</pre></div>

745

</div>

746

For a composite index, multiple columns can be specified:

747

<div class="highlight-python"><div class="highlight"><pre>Index("some_index", sometable.c.name, sometable.c.address)</pre></div>

748

</div>

749

Functional indexes are supported as well, keeping in mind that at least

750

one <a class="reference internal" href="metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal">Column</tt></a> must be present:

751

<div class="highlight-python"><div class="highlight"><pre>Index("some_index", func.lower(sometable.c.name))</pre></div>

752

</div>

753

754

New in version 0.8: support for functional and expression-based indexes.

755

</div>

756

757

See also

758

<a class="reference internal" href="#schema-indexes">Indexes</a> - General information on <a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>.

759

<a class="reference internal" href="../dialects/postgresql.html#postgresql-indexes">Postgresql-Specific Index Options</a> - PostgreSQL-specific options available for the

760

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct.

761

<a class="reference internal" href="../dialects/mysql.html#mysql-indexes">MySQL Specific Index Options</a> - MySQL-specific options available for the

762

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct.

763

<a class="reference internal" href="../dialects/mssql.html#mssql-indexes">MSSQL-Specific Index Options</a> - MSSQL-specific options available for the

764

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a> construct.

765

</div>

766

767

768

<tt class="descname">__init__</tt><big>(</big>name, *expressions, **kw<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Index.__init__" title="Permalink to this definition">¶</a></dt>

769

<dd>Construct an index object.

770

771

772

773

774

<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">

775

<li>name – The name of the index</li>

776

<li>*expressions – Column or SQL expressions.</li>

777

<li>unique – Defaults to False: create a unique index.</li>

778

<li>**kw – Other keyword arguments may be interpreted by specific dialects.</li>

779

</ul>

780

</td>

781

</tr>

782

</tbody>

783

</table>

784

</dd></dl>

785

786

787

788

789

<dd>Return the connectable associated with this Index.

790

</dd></dl>

791

792

793

794

<tt class="descname">create</tt><big>(</big>bind=None<big>)</big><a class="headerlink" href="#sqlalchemy.schema.Index.create" title="Permalink to this definition">¶</a></dt>

795

<dd>Issue a <tt class="docutils literal">CREATE</tt> statement for this

796

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>, using the given <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a>

797

for connectivity.

798

799

See also

800

<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-meth docutils literal">MetaData.create_all()</tt></a>.

801

</div>

802

</dd></dl>

803

804

805

806

807

<dd>Issue a <tt class="docutils literal">DROP</tt> statement for this

808

<a class="reference internal" href="#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><tt class="xref py py-class docutils literal">Index</tt></a>, using the given <a class="reference internal" href="connections.html#sqlalchemy.engine.Connectable" title="sqlalchemy.engine.Connectable"><tt class="xref py py-class docutils literal">Connectable</tt></a>

809

for connectivity.

810

811

See also

812

<a class="reference internal" href="metadata.html#sqlalchemy.schema.MetaData.drop_all" title="sqlalchemy.schema.MetaData.drop_all"><tt class="xref py py-meth docutils literal">MetaData.drop_all()</tt></a>.

813

</div>

814

</dd></dl>

815

816

</dd></dl>

817

818

</div>

819

</div>

820

821

</div>

822

823

</div>

824

825

826

<a href="defaults.html" title="previous chapter">Column Insert/Update Defaults</a>

828

<a href="ddl.html" title="next chapter">Customizing DDL</a>

830

831

832

833

Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.

834

</div>

835

</div>

836

837

</div>

838

839

840

</body>

841

</html>

842

843