Go to Qiita Advent Calendar 2024 Top
LoginSignup
65
57

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@okoppe8

Django モデルフィールド：データベースフィールド 型対応表

Last updated at Posted at 2017-12-13

#この記事について
Djangoでデータベースマイグレーションを実行した時、モデルフィールドがデータベース上のどの型に対応付けられるのかを調査
環境：Django2.0 Postgres9.6 MySQL5.7
Oracleは環境が無いので除外

SQLServerは最近のDjangoバージョンに対応してないので除外
パッケージ「django-pyodbc-azure」を利用することでSQLServerにも接続可能です。環境 SQL Server 2017で確認。

参考

モデルフィールドについては以下の記事を参照

[Django: モデルフィールドリファレンスの一覧]
(https://qiita.com/nachashin/items/f768f0d437e0042dd4b3)

元のモデルクラス

model.py
from django.db import models

# Create your models here.


class Foreign(models.Model):
    pass


class ManyToMany(models.Model):
    pass


class OneToOne(models.Model):
    pass


class Sample(models.Model):

    # 文字列型
    CharField = models.CharField(max_length=100)
    SlugField = models.SlugField()
    URLField = models.URLField()
    EmailField = models.EmailField()
    UUIDField = models.UUIDField()
    FilePathField = models.FilePathField()
    GenericIPAddressField = models.GenericIPAddressField()
    TextField = models.TextField()
    # 数値型
    IntegerField = models.IntegerField()
    SmallIntegerField = models.SmallIntegerField()
    BigIntegerField = models.BigIntegerField()
    PositiveIntegerField = models.PositiveIntegerField()
    PositiveSmallIntegerField = models.PositiveSmallIntegerField()
    FloatField = models.FloatField()
    DecimalField = models.DecimalField(max_digits=5, decimal_places=2)
    # 論理型
    BooleanField = models.BooleanField(default=True)
    NullBooleanField = models.NullBooleanField()
    # 時間型
    TimeField = models.TimeField()
    DateField = models.DateField()
    DateTimeField = models.DateTimeField()
    DurationField = models.DurationField()
    # バイナリ型
    BinaryField = models.BinaryField()
    # ストレージ連動型
    FileField = models.FileField()
    ImageField = models.ImageField()
    # リレーション
    ForeignKey = models.ForeignKey(Foreign, on_delete=models.CASCADE)
    OneToOneField = models.OneToOneField(OneToOne, on_delete=models.CASCADE)
    ManyToManyField = models.ManyToManyField(ManyToMany)

#PostgreSQL

対応表

ModelField DatabaseField NOTNULL CONSTRAINT INDEX
id serial NOT NULL PRIMARY KEY ○
CharField varchar NOT NULL
SlugField varchar(50) NOT NULL ○
URLField varchar(200) NOT NULL
EmailField varchar(254) NOT NULL
UUIDField uuid NOT NULL
FilePathField varchar(100) NOT NULL
GenericIPAddressField inet NOT NULL
TextField text NOT NULL
IntegerField integer NOT NULL
SmallIntegerField smallint NOT NULL
BigIntegerField bigint NOT NULL
PositiveIntegerField integer NOT NULL CHECK ( >= 0)
PositiveSmallIntegerField smallint NOT NULL CHECK ( >= 0)
FloatField double precision NOT NULL
DecimalField numeric NOT NULL
BooleanField boolean NOT NULL
NullBooleanField boolean NULL
TimeField time NOT NULL
DateField date NOT NULL
DateTimeField timestamp with time zone NOT NULL
DurationField interval NOT NULL
BinaryField bytea NOT NULL
FileField varchar(100) NOT NULL
ImageField varchar(100) NOT NULL
ForeignKey_id integer NOT NULL ○
OneToOneField_id integer NOT NULL UNIQUE ○

SQL(PostgreSQL)

BEGIN; 

--
-- Create model Foreign
--
CREATE TABLE "myapp_foreign" ("id" serial NOT NULL PRIMARY KEY); 

--
-- Create model ManyToMany
--
CREATE TABLE "myapp_manytomany" ("id" serial NOT NULL PRIMARY KEY); 

--
-- Create model OneToOne
--
CREATE TABLE "myapp_onetoone" ("id" serial NOT NULL PRIMARY KEY); 

--
-- Create model Sample
--
CREATE TABLE "myapp_sample" ( 
  "id" serial NOT NULL PRIMARY KEY
  , "CharField" varchar (100) NOT NULL
  , "SlugField" varchar (50) NOT NULL
  , "URLField" varchar (200) NOT NULL
  , "EmailField" varchar (254) NOT NULL
  , "UUIDField" uuid NOT NULL
  , "FilePathField" varchar (100) NOT NULL
  , "GenericIPAddressField" inet NOT NULL
  , "TextField" text NOT NULL
  , "IntegerField" integer NOT NULL
  , "SmallIntegerField" smallint NOT NULL
  , "BigIntegerField" bigint NOT NULL
  , "PositiveIntegerField" integer NOT NULL CHECK ("PositiveIntegerField" >= 0)
  , "PositiveSmallIntegerField" smallint NOT NULL CHECK ("PositiveSmallIntegerField" >= 0)
  , "FloatField" double precision NOT NULL
  , "DecimalField" numeric (5, 2) NOT NULL
  , "BooleanField" boolean NOT NULL
  , "NullBooleanField" boolean NULL
  , "TimeField" time NOT NULL
  , "DateField" date NOT NULL
  , "DateTimeField" timestamp with time zone NOT NULL
  , "DurationField" interval NOT NULL
  , "BinaryField" bytea NOT NULL
  , "FileField" varchar (100) NOT NULL
  , "ImageField" varchar (100) NOT NULL
  , "ForeignKey_id" integer NOT NULL
  , "OneToOneField_id" integer NOT NULL UNIQUE
); 

CREATE TABLE "myapp_sample_ManyToManyField" ( 
  "id" serial NOT NULL PRIMARY KEY
  , "sample_id" integer NOT NULL
  , "manytomany_id" integer NOT NULL
); 

ALTER TABLE "myapp_sample" ADD CONSTRAINT "myapp_sample_ForeignKey_id_09008ff8_fk_myapp_foreign_id" 
FOREIGN KEY ("ForeignKey_id") REFERENCES "myapp_foreign" ("id") DEFERRABLE INITIALLY DEFERRED; 

ALTER TABLE "myapp_sample" ADD CONSTRAINT "myapp_sample_OneToOneField_id_9e8cdf39_fk_myapp_onetoone_id"
 FOREIGN KEY ("OneToOneField_id") REFERENCES "myapp_onetoone" ("id") DEFERRABLE INITIALLY DEFERRED; 

CREATE INDEX "myapp_sample_SlugField_4b3b6bbf" 
  ON "myapp_sample" ("SlugField"); 

CREATE INDEX "myapp_sample_SlugField_4b3b6bbf_like" 
  ON "myapp_sample" ("SlugField" varchar_pattern_ops); 

CREATE INDEX "myapp_sample_ForeignKey_id_09008ff8" 
  ON "myapp_sample" ("ForeignKey_id"); 

ALTER TABLE "myapp_sample_ManyToManyField" ADD CONSTRAINT "myapp_sample_ManyToM_sample_id_379d0abf_fk_myapp_sam"
 FOREIGN KEY ("sample_id") REFERENCES "myapp_sample" ("id") DEFERRABLE INITIALLY DEFERRED; 

ALTER TABLE "myapp_sample_ManyToManyField" ADD CONSTRAINT "myapp_sample_ManyToM_manytomany_id_2d47e8ad_fk_myapp_man"
 FOREIGN KEY ("manytomany_id") REFERENCES "myapp_manytomany" ("id") DEFERRABLE INITIALLY DEFERRED; 

ALTER TABLE "myapp_sample_ManyToManyField" ADD CONSTRAINT myapp_sample_ManyToManyF_sample_id_manytomany_id_cd20a596_uniq
 UNIQUE ("sample_id", "manytomany_id"); 

CREATE INDEX "myapp_sample_ManyToManyField_sample_id_379d0abf" 
  ON "myapp_sample_ManyToManyField" ("sample_id"); 

CREATE INDEX "myapp_sample_ManyToManyField_manytomany_id_2d47e8ad" 
  ON "myapp_sample_ManyToManyField" ("manytomany_id"); 

COMMIT;

#MySQL
##対応表

ModelField DatabaseField NOTNULL CONSTRAINT INDEX
id integer NOT NULL AUTO_INCREMENT, PRIMARY KEY ○
CharField varchar NOT NULL
SlugField varchar(50) NOT NULL ○
URLField varchar(200) NOT NULL
EmailField varchar(254) NOT NULL
UUIDField char(32) NOT NULL
FilePathField varchar(100) NOT NULL
GenericIPAddressField char(39) NOT NULL
TextField longtext NOT NULL
IntegerField integer NOT NULL
SmallIntegerField smallint NOT NULL
BigIntegerField bigint NOT NULL
PositiveIntegerField integer NOT NULL UNSIGNED
PositiveSmallIntegerField smallint NOT NULL UNSIGNED
FloatField double precision NOT NULL
DecimalField numeric NOT NULL
BooleanField bool NOT NULL
NullBooleanField bool NULL
TimeField time(6) NOT NULL
DateField date NOT NULL
DateTimeField datetime(6) NOT NULL
DurationField bigint NOT NULL
BinaryField longblob NOT NULL
FileField varchar(100) NOT NULL
ImageField varchar(100) NOT NULL
ForeignKey_id integer NOT NULL ○
OneToOneField_id integer NOT NULL UNIQUE ○

SQL(MySQL)


BEGIN; 

--
-- Create model Foreign
--
CREATE TABLE `myapp_foreign` ( 
  `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY
); 

--
-- Create model ManyToMany
--
CREATE TABLE `myapp_manytomany` ( 
  `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY
); 

--
-- Create model OneToOne
--
CREATE TABLE `myapp_onetoone` ( 
  `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY
); 

--
-- Create model Sample
--
CREATE TABLE `myapp_sample` ( 
  `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY
  , `CharField` varchar (100) NOT NULL
  , `SlugField` varchar (50) NOT NULL
  , `URLField` varchar (200) NOT NULL
  , `EmailField` varchar (254) NOT NULL
  , `UUIDField` char (32) NOT NULL
  , `FilePathField` varchar (100) NOT NULL
  , `GenericIPAddressField` char (39) NOT NULL
  , `TextField` longtext NOT NULL
  , `IntegerField` integer NOT NULL
  , `SmallIntegerField` smallint NOT NULL
  , `BigIntegerField` bigint NOT NULL
  , `PositiveIntegerField` integer UNSIGNED NOT NULL
  , `PositiveSmallIntegerField` smallint UNSIGNED NOT NULL
  , `FloatField` double precision NOT NULL
  , `DecimalField` numeric (5, 2) NOT NULL
  , `BooleanField` bool NOT NULL
  , `NullBooleanField` bool NULL
  , `TimeField` time (6) NOT NULL
  , `DateField` date NOT NULL
  , `DateTimeField` datetime(6) NOT NULL
  , `DurationField` bigint NOT NULL
  , `BinaryField` longblob NOT NULL
  , `FileField` varchar (100) NOT NULL
  , `ImageField` varchar (100) NOT NULL
  , `ForeignKey_id` integer NOT NULL
  , `OneToOneField_id` integer NOT NULL UNIQUE
); 

CREATE TABLE `myapp_sample_ManyToManyField` ( 
  `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY
  , `sample_id` integer NOT NULL
  , `manytomany_id` integer NOT NULL
); 

ALTER TABLE `myapp_sample` ADD CONSTRAINT `myapp_sample_ForeignKey_id_09008ff8_fk_myapp_foreign_id` 
FOREIGN KEY (`ForeignKey_id`) REFERENCES `myapp_foreign` (`id`); 

ALTER TABLE `myapp_sample` ADD CONSTRAINT `myapp_sample_OneToOneField_id_9e8cdf39_fk_myapp_onetoone_id`
 FOREIGN KEY (`OneToOneField_id`) REFERENCES `myapp_onetoone` (`id`); 

CREATE INDEX `myapp_sample_SlugField_4b3b6bbf` 
  ON `myapp_sample` (`SlugField`); 

ALTER TABLE `myapp_sample_ManyToManyField` ADD CONSTRAINT `myapp_sample_ManyToM_sample_id_379d0abf_fk_myapp_sam`
 FOREIGN KEY (`sample_id`) REFERENCES `myapp_sample` (`id`); 

ALTER TABLE `myapp_sample_ManyToManyField` ADD CONSTRAINT `myapp_sample_ManyToM_manytomany_id_2d47e8ad_fk_myapp_man`
 FOREIGN KEY (`manytomany_id`) REFERENCES `myapp_manytomany` (`id`); 

ALTER TABLE `myapp_sample_ManyToManyField` ADD CONSTRAINT myapp_sample_ManyToManyF_sample_id_manytomany_id_cd20a596_uniq
 UNIQUE (`sample_id`, `manytomany_id`); 

COMMIT;

SQLite3

対応表

ModelField DatabaseField NOTNULL CONSTRAINT INDEX
id integer NOT NULL PRIMARY KEY AUTOINCREMENT ○
CharField varchar NOT NULL
SlugField varchar(50) NOT NULL ○
URLField varchar(200) NOT NULL
EmailField varchar(254) NOT NULL
UUIDField char(32) NOT NULL
FilePathField varchar(100) NOT NULL
GenericIPAddressField char(39) NOT NULL
TextField text NOT NULL
IntegerField integer NOT NULL
SmallIntegerField smallint NOT NULL
BigIntegerField bigint NOT NULL
PositiveIntegerField integer unsigned NOT NULL
PositiveSmallIntegerField smallint unsigned NOT NULL
FloatField real NOT NULL
DecimalField decimal NOT NULL
BooleanField bool NOT NULL
NullBooleanField bool NULL
TimeField time NOT NULL
DateField date NOT NULL
DateTimeField datetime NOT NULL
DurationField bigint NOT NULL
BinaryField BLOB NOT NULL
FileField varchar(100) NOT NULL
ImageField varchar(100) NOT NULL
ForeignKey_id integer NOT NULL REFERENCES ○
OneToOneField_id integer NOT NULL UNIQUE REFERENCES ○

SQL(SQLite3)


BEGIN; 

--
-- Create model Foreign
--
CREATE TABLE "myapp_foreign" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT); 

--
-- Create model ManyToMany
--
CREATE TABLE "myapp_manytomany" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT); 

--
-- Create model OneToOne
--
CREATE TABLE "myapp_onetoone" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT); 

--
-- Create model Sample
--
CREATE TABLE "myapp_sample" ( 
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT
  , "CharField" varchar (100) NOT NULL
  , "SlugField" varchar (50) NOT NULL
  , "URLField" varchar (200) NOT NULL
  , "EmailField" varchar (254) NOT NULL
  , "UUIDField" char (32) NOT NULL
  , "FilePathField" varchar (100) NOT NULL
  , "GenericIPAddressField" char (39) NOT NULL
  , "TextField" text NOT NULL
  , "IntegerField" integer NOT NULL
  , "SmallIntegerField" smallint NOT NULL
  , "BigIntegerField" bigint NOT NULL
  , "PositiveIntegerField" integer unsigned NOT NULL
  , "PositiveSmallIntegerField" smallint unsigned NOT NULL
  , "FloatField" real NOT NULL
  , "DecimalField" decimal NOT NULL
  , "BooleanField" bool NOT NULL
  , "NullBooleanField" bool NULL
  , "TimeField" time NOT NULL
  , "DateField" date NOT NULL
  , "DateTimeField" datetime NOT NULL
  , "DurationField" bigint NOT NULL
  , "BinaryField" BLOB NOT NULL
  , "FileField" varchar (100) NOT NULL
  , "ImageField" varchar (100) NOT NULL
  , "ForeignKey_id" integer NOT NULL REFERENCES "myapp_foreign" ("id") DEFERRABLE INITIALLY DEFERRED
  , "OneToOneField_id" integer NOT NULL UNIQUE REFERENCES "myapp_onetoone" ("id") DEFERRABLE INITIALLY
   DEFERRED
); 

CREATE TABLE "myapp_sample_ManyToManyField" ( 
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT
  , "sample_id" integer NOT NULL REFERENCES "myapp_sample" ("id") DEFERRABLE INITIALLY DEFERRED
  , "manytomany_id" integer NOT NULL REFERENCES "myapp_manytomany" ("id") DEFERRABLE INITIALLY DEFERRED
); 

CREATE INDEX "myapp_sample_SlugField_4b3b6bbf" 
  ON "myapp_sample" ("SlugField"); 

CREATE INDEX "myapp_sample_ForeignKey_id_09008ff8" 
  ON "myapp_sample" ("ForeignKey_id"); 

CREATE UNIQUE INDEX myapp_sample_ManyToManyField_sample_id_manytomany_id_cd20a596_uniq 
  ON "myapp_sample_ManyToManyField" ("sample_id", "manytomany_id"); 

CREATE INDEX "myapp_sample_ManyToManyField_sample_id_379d0abf" 
  ON "myapp_sample_ManyToManyField" ("sample_id"); 

CREATE INDEX "myapp_sample_ManyToManyField_manytomany_id_2d47e8ad" 
  ON "myapp_sample_ManyToManyField" ("manytomany_id"); 

COMMIT;

SQL Server

対応表

ModelField DatabaseField NOTNULL CONSTRAINT INDEX
id int IDENTITY (1, 1) NOT NULL PRIMARY KEY ○
CharField nvarchar(100) NOT NULL
SlugField nvarchar(50) NOT NULL ○
URLField nvarchar(200) NOT NULL
EmailField nvarchar(254) NOT NULL
UUIDField char(32) NOT NULL
FilePathField nvarchar(100) NOT NULL
GenericIPAddressField nvarchar(39) NOT NULL
TextField nvarchar(max) NOT NULL
IntegerField int NOT NULL
SmallIntegerField smallint NOT NULL
BigIntegerField bigint NOT NULL
PositiveIntegerField int CONSTRAINT ~ CHECK([PositiveIntegerField] >= 0) NOT NULL
PositiveSmallIntegerField smallint CONSTRAINT ~ CHECK ([PositiveSmallIntegerField] >= 0) NOT NULL
FloatField double precision NOT NULL
DecimalField numeric (5, 2) NOT NULL
BooleanField bit NOT NULL
NullBooleanField bit NULL
TimeField time NOT NULL
DateField date NOT NULL
DateTimeField datetime2 NOT NULL
DurationField bigint NOT NULL
BinaryField varbinary(max) NOT NULL
FileField nvarchar(100) NOT NULL
ImageField nvarchar(100) NOT NULL
ForeignKey_id int NOT NULL ○
OneToOneField_id int NOT NULL UNIQUE ○ 
BEGIN TRANSACTION                                 --
-- Create model Foreign
--
CREATE TABLE [api_foreign] ([id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY); 

--
-- Create model ManyToMany
--
CREATE TABLE [api_manytomany] ([id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY); 

--
-- Create model OneToOne
--
CREATE TABLE [api_onetoone] ([id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY); 

--
-- Create model Sample
--
CREATE TABLE [api_sample] ( 
  [id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY
  , [CharField] nvarchar(100) NOT NULL
  , [SlugField] nvarchar(50) NOT NULL
  , [URLField] nvarchar(200) NOT NULL
  , [EmailField] nvarchar(254) NOT NULL
  , [UUIDField] char (32) NOT NULL
  , [FilePathField] nvarchar(100) NOT NULL
  , [GenericIPAddressField] nvarchar(39) NOT NULL
  , [TextField] nvarchar(max) NOT NULL
  , [IntegerField] int NOT NULL
  , [SmallIntegerField] smallint NOT NULL
  , [BigIntegerField] bigint NOT NULL
  , [PositiveIntegerField] int NOT NULL CONSTRAINT api_sample_PositiveIntegerField_c8107e2d_check CHECK
   ([PositiveIntegerField] >= 0)
  , [PositiveSmallIntegerField] smallint NOT NULL CONSTRAINT api_sample_PositiveSmallIntegerField_03485122_check
   CHECK ([PositiveSmallIntegerField] >= 0)
  , [FloatField] double precision NOT NULL
  , [DecimalField] numeric (5, 2) NOT NULL
  , [BooleanField] bit NOT NULL
  , [NullBooleanField] bit NULL
  , [TimeField] time NOT NULL
  , [DateField] date NOT NULL
  , [DateTimeField] datetime2 NOT NULL
  , [DurationField] bigint NOT NULL
  , [BinaryField] varbinary(max) NOT NULL
  , [FileField] nvarchar(100) NOT NULL
  , [ImageField] nvarchar(100) NOT NULL
  , [ForeignKey_id] int NOT NULL
  , [OneToOneField_id] int NOT NULL UNIQUE
); 

CREATE TABLE [api_sample_ManyToManyField] ( 
  [id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY
  , [sample_id] int NOT NULL
  , [manytomany_id] int NOT NULL
); 

ALTER TABLE [api_sample] ADD CONSTRAINT [api_sample_ForeignKey_id_e1233a0a_fk_api_foreign_id] FOREIGN
 KEY ([ForeignKey_id]) REFERENCES [api_foreign] ([id]); 

ALTER TABLE [api_sample] ADD CONSTRAINT [api_sample_OneToOneField_id_9c34197c_fk_api_onetoone_id] FOREIGN
 KEY ([OneToOneField_id]) REFERENCES [api_onetoone] ([id]); 

CREATE INDEX [api_sample_SlugField_32311b88] 
  ON [api_sample] ([SlugField]); 

CREATE INDEX [api_sample_ForeignKey_id_e1233a0a] 
  ON [api_sample] ([ForeignKey_id]); 

ALTER TABLE [api_sample_ManyToManyField] ADD CONSTRAINT [api_sample_ManyToManyField_sample_id_d0384bdf_fk_api_sample_id]
 FOREIGN KEY ([sample_id]) REFERENCES [api_sample] ([id]); 

ALTER TABLE [api_sample_ManyToManyField] ADD CONSTRAINT [api_sample_ManyToManyField_manytomany_id_2387c5f7_fk_api_manytomany_id]
 FOREIGN KEY ([manytomany_id]) REFERENCES [api_manytomany] ([id]); 

ALTER TABLE [api_sample_ManyToManyField] ADD CONSTRAINT api_sample_ManyToManyField_sample_id_manytomany_id_66194079_uniq
 UNIQUE ([sample_id], [manytomany_id]); 

CREATE INDEX [api_sample_ManyToManyField_sample_id_d0384bdf] 
  ON [api_sample_ManyToManyField] ([sample_id]); 

CREATE INDEX [api_sample_ManyToManyField_manytomany_id_2387c5f7] 
  ON [api_sample_ManyToManyField] ([manytomany_id]); 

COMMIT;
65
57
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
65
57

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?