Python sqlalchemy.schema.AddConstraint() Examples

def test_external_ck_constraint_cancels_internal(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint(
            "a < b",
            name="my_test_constraint",
            deferrable=True,
            initially="DEFERRED",
            table=t,
        )

        schema.AddConstraint(constraint)

        # once we make an AddConstraint,
        # inline compilation of the CONSTRAINT
        # is disabled
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl (" "a INTEGER, " "b INTEGER" ")",
        ) 

def test_exclude_constraint_full(self):
        m = MetaData()
        room = Column("room", Integer, primary_key=True)
        tbl = Table("testtbl", m, room, Column("during", TSRANGE))
        room = Column("room", Integer, primary_key=True)
        cons = ExcludeConstraint(
            (room, "="),
            ("during", "&&"),
            name="my_name",
            using="gist",
            where="room > 100",
            deferrable=True,
            initially="immediate",
        )
        tbl.append_constraint(cons)
        self.assert_compile(
            schema.AddConstraint(cons),
            "ALTER TABLE testtbl ADD CONSTRAINT my_name "
            "EXCLUDE USING gist "
            "(room WITH =, during WITH "
            "&&) WHERE "
            "(room > 100) DEFERRABLE INITIALLY immediate",
            dialect=postgresql.dialect(),
        ) 

def test_render_ck_constraint_external(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint(
            "a < b",
            name="my_test_constraint",
            deferrable=True,
            initially="DEFERRED",
            table=t,
        )

        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD CONSTRAINT my_test_constraint "
            "CHECK (a < b) DEFERRABLE INITIALLY DEFERRED",
        ) 

def test_exclude_constraint_copy_where_using(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("room", Integer, primary_key=True))
        cons = ExcludeConstraint(
            (tbl.c.room, "="), where=tbl.c.room > 5, using="foobar"
        )
        tbl.append_constraint(cons)
        self.assert_compile(
            schema.AddConstraint(cons),
            "ALTER TABLE testtbl ADD EXCLUDE USING foobar "
            "(room WITH =) WHERE (testtbl.room > 5)",
        )

        m2 = MetaData()
        tbl2 = tbl.to_metadata(m2)
        self.assert_compile(
            schema.CreateTable(tbl2),
            "CREATE TABLE testtbl (room SERIAL NOT NULL, "
            "PRIMARY KEY (room), "
            "EXCLUDE USING foobar "
            "(room WITH =) WHERE (testtbl.room > 5))",
        ) 

def test_render_ck_constraint_inline(self):
        t, t2 = self._constraint_create_fixture()

        CheckConstraint(
            "a < b",
            name="my_test_constraint",
            deferrable=True,
            initially="DEFERRED",
            table=t,
        )

        # before we create an AddConstraint,
        # the CONSTRAINT comes out inline
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl ("
            "a INTEGER, "
            "b INTEGER, "
            "CONSTRAINT my_test_constraint CHECK (a < b) "
            "DEFERRABLE INITIALLY DEFERRED"
            ")",
        ) 

def test_fk_illegal_sql_phrases(self):
        a = Table("a", MetaData(), Column("q", Integer))
        b = Table("b", MetaData(), Column("p", Integer))

        for kw in ("onupdate", "ondelete", "initially"):
            for phrase in (
                "NOT SQL",
                "INITALLY NOT SQL",
                "FOO RESTRICT",
                "CASCADE WRONG",
                "SET  NULL",
            ):
                const = schema.AddConstraint(
                    schema.ForeignKeyConstraint(
                        [a.c.q], [b.c.p], **{kw: phrase}
                    )
                )
                assert_raises_message(
                    exc.CompileError,
                    r"Unexpected SQL phrase: '%s'" % phrase,
                    const.compile,
                ) 

def test_uq_conv_name(self):
        u1 = self._fixture(
            naming_convention={"uq": "uq_%(table_name)s_%(column_0_name)s"}
        )
        uq = UniqueConstraint(u1.c.data, name=naming.conv("myname"))
        self.assert_compile(
            schema.AddConstraint(uq),
            'ALTER TABLE "user" ADD CONSTRAINT myname UNIQUE (data)',
            dialect="default",
        ) 

def test_render_add_pk_constraint(self):
        t, t2 = self._constraint_create_fixture()

        assert t.c.a.primary_key is False
        constraint = PrimaryKeyConstraint(t.c.a)
        assert t.c.a.primary_key is True
        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD PRIMARY KEY (a)",
        ) 

def test_render_check_constraint_sql_literal(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint(t.c.a > 5)

        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD CHECK (a > 5)",
        ) 

def test_fk_render(self):
        a = Table("a", MetaData(), Column("q", Integer))
        b = Table("b", MetaData(), Column("p", Integer))

        self.assert_compile(
            schema.AddConstraint(
                schema.ForeignKeyConstraint([a.c.q], [b.c.p])
            ),
            "ALTER TABLE a ADD FOREIGN KEY(q) REFERENCES b (p)",
        )

        self.assert_compile(
            schema.AddConstraint(
                schema.ForeignKeyConstraint(
                    [a.c.q], [b.c.p], onupdate="SET NULL", ondelete="CASCADE"
                )
            ),
            "ALTER TABLE a ADD FOREIGN KEY(q) REFERENCES b (p) "
            "ON DELETE CASCADE ON UPDATE SET NULL",
        )

        self.assert_compile(
            schema.AddConstraint(
                schema.ForeignKeyConstraint(
                    [a.c.q], [b.c.p], initially="DEFERRED"
                )
            ),
            "ALTER TABLE a ADD FOREIGN KEY(q) REFERENCES b (p) "
            "INITIALLY DEFERRED",
        ) 

def test_colliding_col_label_from_unique_obj(self):
        t1 = self._colliding_name_fixture({"uq": "uq_%(column_0_label)s"}, {})
        uq = UniqueConstraint(t1.c.id)
        const = [c for c in t1.constraints if isinstance(c, UniqueConstraint)]
        is_(const[0], uq)
        eq_(const[0].name, "uq_foo_id")
        self.assert_compile(
            AddConstraint(const[0]),
            "ALTER TABLE foo ADD CONSTRAINT uq_foo_id UNIQUE (id)",
        ) 

def test_colliding_col_label_from_unique_flag_no_conv(self):
        t1 = self._colliding_name_fixture({"ck": "foo"}, {"unique": True})

        const = [c for c in t1.constraints if isinstance(c, UniqueConstraint)]
        is_(const[0].name, None)

        self.assert_compile(
            AddConstraint(const[0]), "ALTER TABLE foo ADD UNIQUE (id)"
        ) 

def test_render_add_fk_constraint_stringcol(self):
        t, t2 = self._constraint_create_fixture()

        constraint = ForeignKeyConstraint(["b"], ["t2.a"])
        t.append_constraint(constraint)
        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD FOREIGN KEY(b) REFERENCES t2 (a)",
        ) 

def test_uq_defer_name_no_convention(self):
        u1 = self._fixture(naming_convention={})
        uq = UniqueConstraint(u1.c.data, name=naming._defer_name("myname"))
        self.assert_compile(
            schema.AddConstraint(uq),
            'ALTER TABLE "user" ADD CONSTRAINT myname UNIQUE (data)',
            dialect="default",
        ) 

def test_uq_defer_name_convention(self):
        u1 = self._fixture(
            naming_convention={"uq": "uq_%(table_name)s_%(column_0_name)s"}
        )
        uq = UniqueConstraint(u1.c.data, name=naming._defer_name("myname"))
        self.assert_compile(
            schema.AddConstraint(uq),
            'ALTER TABLE "user" ADD CONSTRAINT uq_user_data UNIQUE (data)',
            dialect="default",
        ) 

def test_fk_allcols_underscore_name(self):
        u1 = self._fixture(
            naming_convention={
                "fk": "fk_%(table_name)s_%(column_0_N_name)s_"
                "%(referred_table_name)s_%(referred_column_0_N_name)s"
            }
        )

        m1 = u1.metadata
        a1 = Table(
            "address",
            m1,
            Column("id", Integer, primary_key=True),
            Column("UserData", String(30), key="user_data"),
            Column("UserData2", String(30), key="user_data2"),
            Column("UserData3", String(30), key="user_data3"),
        )
        fk = ForeignKeyConstraint(
            ["user_data", "user_data2", "user_data3"],
            ["user.data", "user.data2", "user.data3"],
        )
        a1.append_constraint(fk)
        self.assert_compile(
            schema.AddConstraint(fk),
            "ALTER TABLE address ADD CONSTRAINT "
            '"fk_address_UserData_UserData2_UserData3_user_data_Data2_Data3" '
            'FOREIGN KEY("UserData", "UserData2", "UserData3") '
            'REFERENCES "user" (data, "Data2", "Data3")',
            dialect=default.DefaultDialect(),
        ) 

def test_fk_allcols_merged_name(self):
        u1 = self._fixture(
            naming_convention={
                "fk": "fk_%(table_name)s_%(column_0N_name)s_"
                "%(referred_table_name)s_%(referred_column_0N_name)s"
            }
        )

        m1 = u1.metadata
        a1 = Table(
            "address",
            m1,
            Column("id", Integer, primary_key=True),
            Column("UserData", String(30), key="user_data"),
            Column("UserData2", String(30), key="user_data2"),
            Column("UserData3", String(30), key="user_data3"),
        )
        fk = ForeignKeyConstraint(
            ["user_data", "user_data2", "user_data3"],
            ["user.data", "user.data2", "user.data3"],
        )
        a1.append_constraint(fk)
        self.assert_compile(
            schema.AddConstraint(fk),
            "ALTER TABLE address ADD CONSTRAINT "
            '"fk_address_UserDataUserData2UserData3_user_dataData2Data3" '
            'FOREIGN KEY("UserData", "UserData2", "UserData3") '
            'REFERENCES "user" (data, "Data2", "Data3")',
            dialect=default.DefaultDialect(),
        ) 

def test_fk_allcols_truncated_name(self):
        u1 = self._fixture(
            naming_convention={
                "fk": "fk_%(table_name)s_%(column_0N_name)s_"
                "%(referred_table_name)s_%(referred_column_0N_name)s"
            }
        )

        m1 = u1.metadata
        a1 = Table(
            "address",
            m1,
            Column("id", Integer, primary_key=True),
            Column("UserData", String(30), key="user_data"),
            Column("UserData2", String(30), key="user_data2"),
            Column("UserData3", String(30), key="user_data3"),
        )
        fk = ForeignKeyConstraint(
            ["user_data", "user_data2", "user_data3"],
            ["user.data", "user.data2", "user.data3"],
        )
        a1.append_constraint(fk)

        dialect = default.DefaultDialect()
        dialect.max_identifier_length = 15
        self.assert_compile(
            schema.AddConstraint(fk),
            "ALTER TABLE address ADD CONSTRAINT "
            "fk_addr_f9ff "
            'FOREIGN KEY("UserData", "UserData2", "UserData3") '
            'REFERENCES "user" (data, "Data2", "Data3")',
            dialect=dialect,
        ) 

def test_ck_name_deferred_required(self):
        u1 = self._fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        ck = CheckConstraint(u1.c.data == "x", name=naming._defer_name(None))

        assert_raises_message(
            exc.InvalidRequestError,
            r"Naming convention including %\(constraint_name\)s token "
            "requires that constraint is explicitly named.",
            schema.AddConstraint(ck).compile,
        ) 

def add_constraint(self, const):
        if const._create_rule is None or \
                const._create_rule(self):
            self._exec(schema.AddConstraint(const)) 

def test_render_add_uq_constraint_realcol(self):
        t, t2 = self._constraint_create_fixture()

        constraint = UniqueConstraint(t2.c.a, t2.c.b, name="uq_cs2")
        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE t2 ADD CONSTRAINT uq_cs2 UNIQUE (a, b)",
        ) 

def test_render_add_fk_constraint_realcol(self):
        t, t2 = self._constraint_create_fixture()

        constraint = ForeignKeyConstraint([t.c.a], [t2.c.b])
        t.append_constraint(constraint)
        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD FOREIGN KEY(a) REFERENCES t2 (b)",
        ) 

def add_constraint(self, const):
        if const._create_rule is None or \
                const._create_rule(self):
            self._exec(schema.AddConstraint(const)) 

def _assert_create_w_alter(self, elements, generator, argument):
        self._assert_ddl(
            (schema.CreateTable, schema.CreateSequence, schema.AddConstraint),
            elements,
            generator,
            argument,
        ) 

def test_exclude_constraint_when(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("room", String))
        cons = ExcludeConstraint(("room", "="), where=tbl.c.room.in_(["12"]))
        tbl.append_constraint(cons)
        self.assert_compile(
            schema.AddConstraint(cons),
            "ALTER TABLE testtbl ADD EXCLUDE USING gist "
            "(room WITH =) WHERE (testtbl.room IN ('12'))",
            dialect=postgresql.dialect(),
        ) 

def test_exclude_constraint_cast_quote(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("Room", String))
        cons = ExcludeConstraint((cast(tbl.c.Room, Text), "="))
        tbl.append_constraint(cons)
        self.assert_compile(
            schema.AddConstraint(cons),
            "ALTER TABLE testtbl ADD EXCLUDE USING gist "
            '(CAST("Room" AS TEXT) WITH =)',
        ) 

def test_exclude_constraint_cast(self):
        m = MetaData()
        tbl = Table("testtbl", m, Column("room", String))
        cons = ExcludeConstraint((cast(tbl.c.room, Text), "="))
        tbl.append_constraint(cons)
        self.assert_compile(
            schema.AddConstraint(cons),
            "ALTER TABLE testtbl ADD EXCLUDE USING gist "
            "(CAST(room AS TEXT) WITH =)",
        ) 

def test_exclude_constraint_with_using_unusual_conditions(self):
        m = MetaData()
        cons = ExcludeConstraint(("q", "="), using="not a keyword")
        Table("testtbl", m, Column("q", String), cons)
        assert_raises_message(
            exc.CompileError,
            "Unexpected SQL phrase: 'not a keyword'",
            schema.AddConstraint(cons).compile,
            dialect=postgresql.dialect(),
        ) 

def test_exclude_constraint_text(self):
        m = MetaData()
        cons = ExcludeConstraint((text("room::TEXT"), "="))
        Table("testtbl", m, Column("room", String), cons)
        self.assert_compile(
            schema.AddConstraint(cons),
            "ALTER TABLE testtbl ADD EXCLUDE USING gist "
            "(room::TEXT WITH =)",
        ) 

def test_exclude_constraint_copy(self):
        m = MetaData()
        cons = ExcludeConstraint(("room", "="))
        tbl = Table(
            "testtbl", m, Column("room", Integer, primary_key=True), cons
        )
        # apparently you can't copy a ColumnCollectionConstraint until
        # after it has been bound to a table...
        cons_copy = cons.copy()
        tbl.append_constraint(cons_copy)
        self.assert_compile(
            schema.AddConstraint(cons_copy),
            "ALTER TABLE testtbl ADD EXCLUDE USING gist " "(room WITH =)",
        )