CODEWITHSUNDEEP
djangodatabasesqlite
Julian Hernandez
Julian Hernandez

Reputation: 23

Is possible to make unique a field in the database for a foreign key?

Lets suppose that I have two models named Company and Airplane:

class Company(models.Model):
    name= models.CharField(max_length=250)
    location=models.Charfield(max_length=250)

class Airplane(models.Model):  
    company = models.ForeignKey(Company, on_delete=models.RESTRICT)
    number = models.IntegerField(unique=True)

I want the number in Airplane model to be unique but just in the same company, an airplane from another company can have the same number. Is it that possible?

Upvotes: 2

Views: 706

Answers (2)

psolomon
psolomon

Reputation: 173

Rather than requiring Airplane number field to be unique, you could enforce a multiple-column uniqueness constraint. In other words, the (company, number) pair should be unique and another default auto-incremented column could be used as a primary key for the Airplane table.

See the answer here: How to define two fields "unique" as couple

Upvotes: 2

MikeT
MikeT

Reputation: 56948

You could create a UNIQUE Index (at least in native SQLite) for the airplane table comprised of the company and number columns.

I haven't used Django so I'm unsure whether or not a multi-column index is possible.

However for native SQLite code here is some that demonstrates the underlying principle/operation:-

DROP INDEX IF EXISTS airplane_number_company;
DROP TABLE IF EXISTS airplane;
DROP TABLE IF EXISTS company;
CREATE TABLE IF NOT EXISTS company (name TEXT UNIQUE, location TEXT);
CREATE TABLE IF NOT EXISTS airplane (company REFERENCES company(name), number INTEGER);
CREATE UNIQUE INDEX IF NOT EXISTS airplane_number_company ON airplane(company,number);

INSERT INTO company VALUES('company1','England'),('company2','America');
INSERT INTO airplane VALUES('company1',100),('company1',200),('company2',100),('company2',200);
SELECT * FROM airplane JOIN company ON airplane.company = company.name;
INSERT OR IGNORE INTO airplane VALUES('company1',100),('company1',500);
SELECT * FROM airplane JOIN company ON airplane.company = company.name;

The first SELECT will display the 4 rows that have been added showing that airplanes with the same number but different company can be added.

enter image description here

After that select an attempt is made to insert two new airplane rows. One row using the same company and number as an existing airplane run the other row using an unused combination.

The second SELECT then displays the result showing that only 1 of the two rows has been added. The fisrt row cannot be added due to the airplane_number_company index the OR IGNORE allowing the failed attempt to be ignored.

enter image description here

The log from running the above is :-

DROP INDEX IF EXISTS airplane_number_company
> OK
> Time: 0s


DROP TABLE IF EXISTS airplane
> OK
> Time: 0s

DROP TABLE IF EXISTS company
> OK
> Time: 0s

CREATE TABLE IF NOT EXISTS company (name TEXT UNIQUE, location TEXT)
> OK
> Time: 0.11s
   
CREATE TABLE IF NOT EXISTS airplane (company REFERENCES company(name), number INTEGER)
> OK
> Time: 0.096s
    
CREATE UNIQUE INDEX IF NOT EXISTS airplane_number_company ON airplane(company,number)
> OK
> Time: 0.085s
    
INSERT INTO company VALUES('company1','England'),('company2','America')
> Affected rows: 2
> Time: 0.073s
    
INSERT INTO airplane VALUES('company1',100),('company1',200),('company2',100),('company2',200)
> Affected rows: 4
> Time: 0.074s
    
SELECT * FROM airplane JOIN company ON airplane.company = company.name
> OK
> Time: 0.001s

INSERT OR IGNORE INTO airplane VALUES('company1',100),('company1',500)
> Affected rows: 1
> Time: 0.082s

SELECT * FROM airplane JOIN company ON airplane.company = company.name
> OK
> Time: 0s

Upvotes: 0

Related Questions