SQLで制約を使用する方法
序章
SQLデータベースを設計するときに、テーブルの特定の列に追加できるデータに制限を課したい場合があります。 SQLは、制約を使用してこれを可能にします。 列またはテーブルに制約を適用した後、制約と一致しないデータを列またはテーブルに追加しようとすると失敗します。
さまざまなSQL実装には、制約を処理する独自の方法があります。 このガイドでは、多くのデータベース管理システムが制約を管理するために使用する構文の概要を説明します。例全体でMySQLを使用します。
前提条件
このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム(RDBMS)を実行しているコンピューターが必要です。 このガイドの手順と例は、次の環境を使用して検証されています。
- Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
- Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。 このガイドは、ステップ3 で説明されているように、新しく作成されたユーザーによって検証されました。
注:多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。
SQL制約とは何か、およびそれらがどのように機能するかについての一般的な理解も役立ちます。 この概念の概要については、SQL制約の理解に関する記事を参照してください。
また、制約付きのテーブルの作成を練習するために使用できるデータベースも必要です。 このようなテストデータベースがない場合は、作成方法の詳細について、次のMySQLへの接続とサンプルデータベースのセットアップセクションを参照してください。
このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!
ボタンをクリックして開始します。
サンプルデータベースへの接続と設定
SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
- ssh sammy@your_server_ip
次に、MySQLサーバープロンプトを開き、sammy
をMySQLユーザーアカウントの名前に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecret
という単語であることに注意してください。
- mysql -u sammy -p
constraintsDB
という名前のデータベースを作成します。
- CREATE DATABASE constraintsDB;
データベースが正常に作成されると、次のような出力が表示されます。
OutputQuery OK, 1 row affected (0.01 sec)
constraintsDB
データベースを選択するには、次のUSE
ステートメントを実行します。
- USE constraintsDB;
OutputDatabase changed
これで、ガイドの残りの部分に従い、SQLで制約を作成および管理する方法について学習を開始する準備が整いました。
制約付きのテーブルの作成
通常、テーブルの作成中に制約を定義します。 次のCREATE TABLE
構文は、empId
、empName
、およびempPhoneNum
の3つの列を持つemployeeInfo
という名前のテーブルを作成します。 このステートメントは、UNIQUE
制約をempId
列にも適用します。 これにより、テーブル内のすべての行がこの列で同じ値を持つ必要がなくなります。
- CREATE TABLE employeeInfo (
- empId int UNIQUE,
- empName varchar(30),
- empPhoneNum int
- );
このステートメントは、empId
列の直後にUNIQUE
制約を定義します。つまり、制約はその列にのみ適用されます。 このテーブルにデータを追加しようとすると、DBMSはempId
のみの既存の内容をチェックして、empId
に追加する新しい値が実際に一意であることを確認します。 これは、列レベルの制約と呼ばれるものです。
列定義の外部で制約を適用することもできます。 次の例では、racersInfo
という名前のテーブルを、racerId
、racerName
、およびfinish
の3つの列で作成します。 列の定義の下では、CHECK
制約をfinish
列に適用して、すべてのレーサーが1
以上のフィニッシュを持つようにします(レーサーは下に配置できないため)最初の場所):
- CREATE TABLE racersInfo (
- racerId int,
- finish int,
- racerName varchar(30),
- CHECK (finish > 0)
- );
制約は個々の列定義の外部で適用されるため、制約を適用する列の名前を括弧で囲む必要があります。 単一の列の定義外で制約を指定する場合は常に、テーブルレベルの制約と呼ばれます。 列レベルの制約は個々の列にのみ適用されますが、このようなテーブルの制約は複数の列に適用または参照できます。
命名の制約
制約を定義するたびに、RDBMSはその名前を自動的に生成します。 この名前は、制約の管理に使用されるコマンドのエラーメッセージで制約を参照するために使用されます。
ただし、データベース管理者が制約に独自の名前を付けると便利な場合があります。 通常、自動生成された制約名はわかりやすいものではないため、自分で名前を付けると、制約の目的を思い出すのに役立ちます。
制約に名前を付けるには、制約タイプの前にCONSTRAINT
キーワードを付け、その後に任意の名前を付けます。 このサンプルステートメントは、racersInfo
テーブルを再作成し、名前をnewRacersInfo
に変更し、CHECK
制約の名前としてnoNegativeFinish
を追加します。
- CREATE TABLE newRacersInfo (
- racerId int,
- finish int,
- racerName varchar(30),
- CONSTRAINT noNegativeFinish
- CHECK (finish >= 1)
- );
注:制約の名前を設定しない場合、または後で忘れた場合は、データベース管理システムの情報スキーマを参照して名前を見つけることができる可能性があります。 。 最近のデータベースシステムやクライアントの多くは、制約の名前を示す内部CREATE
ステートメントを表示するためのショートカットさえ提供しています。
MySQLおよびPostgreSQLに関連するショートカットの公式ドキュメントへのリンクは次のとおりです。
- MySQL :MySQLには
SHOW CREATE TABLE
ステートメントが含まれています。これは、名前付きテーブルを作成したCREATE TABLE
ステートメント全体を返します。
- SHOW CREATE TABLE table_name;
- PostgreSQL :PostgreSQLクライアント
psql
には、特定のテーブルに関する情報を表示するために使用できるいくつかのオプションがあります。\d
オプションは、名前付きテーブルのメタデータを返します。
- \d table_name
制約の管理
MySQLでは、既存のテーブルに制約を追加したり、ALTER TABLE
ステートメントを使用してそれらを削除したりできます。
たとえば、次のコマンドは、前に作成したemployeeInfo
テーブルのempName
列にUNIQUE
制約を追加します。
- ALTER TABLE employeeInfo ADD UNIQUE (empName);
既存のテーブルに制約を追加する場合、CONSTRAINT
キーワードを使用して、制約を識別するための名前を指定することもできます。 この例では、uID
という名前のUNIQUE
制約を、前に作成したracersInfo
テーブルのracerId
列に追加します。
- ALTER TABLE racersInfo ADD CONSTRAINT uID UNIQUE (racerId);
このような制約を追加する前に、新しい制約の条件に違反するレコードを挿入した場合、ALTER TABLE
ステートメントは失敗します。
制約を削除するには、DROP CONSTRAINT
構文を使用し、その後に削除する制約の名前を続けます。 このコマンドは、前のコマンドで作成されたracersPK
制約を削除します。
- ALTER TABLE racersInfo DROP CONSTRAINT uID;
結論
このガイドを読むことで、SQLを使用して列とテーブルに制約を追加および削除する方法を学習しました。 ここに示すコマンドはほとんどのリレーショナルデータベースで機能するはずですが、すべてのSQLデータベースが独自の言語実装を使用していることに注意してください。 各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。
SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。