CODEWITHSUNDEEP
pythonflasksqlalchemy
BYN13K
BYN13K

Reputation: 47

Creating new personal table for user in SQLAlchemy

Can anyone assist me in solving the problem below.

I would like everyone in the Person table have the possibility to have a pet with the same name of somebody else, but one person cannot have two pets with the same name, but I am not sure how to model/code that.

Person: Zbyszek

  1. Pet: Flufi - age: 3
  2. Pet: Chili - age: 2

Person: Jon

  1. Pet: Chili - age: 4

code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///sql1.db'
db = SQLAlchemy(app)

class Person(db.Model):

    __tablename__ = 'person'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)
    pets = db.relationship('Pet', backref='owner')
    
class Pet(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)
    age = db.Column(db.Integer)
    owner_id = db.Column(db.Integer, db.ForeignKey('person.id'))

Upvotes: 1

Views: 105

Answers (1)

Cobble
Cobble

Reputation: 122

You can't have the unique=True tag on the pets if there can be more than one pet with that name, even if they belong to different people. There is no problem in removing it and I don't see why you would need the name to be unique anyway since you obviously want the possibilty of multiple pets with the same name. Since the ID of the pet is unique the name doesn't have to be.

If you now want only one of each pet name available to one person, you to set that as a table argument for the pet. So that no two pets with the same owner can also have the same name.

Updated pet class:

class Pet(db.Model):
    __table_args__ = (db.UniqueConstraint('owner_id ', 'name', name='owner_pet_uc'),)
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))
    age = db.Column(db.Integer)
    owner_id = db.Column(db.Integer, db.ForeignKey('person.id'))

Upvotes: 1

Related Questions