MYSQL Views? Joining two tables

I have two tables. 'users' and 'paying_users'

Paying_users have a bit more information regarding payments of course.

I am looking for a way on which开发者_如何学Python upon insertion I will be able to force a UNIQUE username that is common to both tables. So that if 'Shelly' is on 'users', I will not be able to INSERT 'Shelly' to 'paying_users'. Is that possible on MYSQL?

Why don't you use users as main table and relate the paying_users (which are a part of the users, I reckon) to the id of users? Then, you would not have a problems with duplicates.

users: 
  id: ...
  name: ...
  other information

paying_users:
  id: ...
  users_id: ...
  other information:

Paying_users are still users, right? Then this would solve your problem:

users: 
  id: ...
  name: ...
  other information that relates to a user
  PRIMARY KEY: id
  UNIQUE KEY: name

paying_users:
  id: ...
  other information that relates a paying user only
  PRIMARY KEY: id
  FOREIGN KEY: id
      REFERENCES users(id)

• With this structure, when you want to add a paying_user, you do it in two steps:
  • 1 add him to table users (so his name is unique across all users)
  • 2 add his id to table paying_users, along with other data related to paying users only.

Further advantages:

• When you have a (simple) user that decides to pay, you just do step 2.

• When you have a paying_user that his paying period expired, you remove his record from paying_users table only, so he still remains as a simple user.

I don't think it is possible to do this in MySQL. It would be better to design a single users table if possible. You would then create an auxillary table containing the user id and pyament info for those customers that are paying and you'd be able to join when necessary

Try combining your INSERT with a SELECT. e.g.:

INSERT INTO paying_users
SELECT 'Shelly', 'other', 'column', 'values'
WHERE (SELECT COUNT(1) FROM users WHERE username = 'Shelly') = 0

I'm not sure, having not tried this particular construct, but you may need to create a 1 row dummy table to select your data from:

INSERT INTO paying_users
SELECT 'Shelly', 'other', 'column', 'values'
FROM (SELECT 1) AS dummy
WHERE (SELECT COUNT(1) FROM users WHERE username = 'Shelly') = 0